Time calculations

[Matt]

I'm trying to work out the formula to calculate time variances. I need to
display minutes as a number so it can be included in a CONCATENATE formula.
In addition to this, how do I calculate a sum such as 23:00 - 02:00? When
using a number, or general format in the result cell, excel doesn't return
the rersult I want. 180 (mins)

 

[Pete_UK]

With start date in A1 and finish date in B1, use this:

=MOD(B1-A1,1)*24*60

and format the cell as General to give you 180 (minutes).

To control the format of the number in your concatenation formula, use
something like this:

TEXT(C1,"0.00")

to give you 2 decimal places.

Hope this helps.

Pete

 

[Suleman Peerzade]

Hi,

Hi,

This is for the calculation part in general or number format. While you
enter the data please enter like this
23.00 to 2.00 instead of 23:00 to 2:00
now make sure that the cells are formated to numbers, say for eg that this
data starts from cell A1 so

A1 B1 C1
9.00 18.00 =(B1-A1)*60

This will give the answer in minutes instead of hours.
--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade

 

[Suleman Peerzade]

Hi David,

The OP wanted his answer in minutes (assuming that he/she is aware of *60
which means 60 secs=1min) that too in number format.
When we are entering the data 23:00 excel automatically formats it as
customs HH:MM.
Yes if you blindly put my formula it would give back 12 i assume the
requestor would have been aware of this as to why he/she is getting 12 in
that case.

When i use your formula for 9.00 - 9.20 it give me 288?? when i put it as
9:00 - 9:20 then it gives me 20

I would like to say that i am a learner and i hope even if i am wrong in
some sense i would learn with good people like you in this community.
--
_______________________
Click "Yes" if it helps
________
Thanks
Suleman Peerzade