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

 

[David Biddulph]

=MOD(B2-A2,1)*24*60

 

[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

 

[David Biddulph]

The OP might wish to consider whether this suggestion from Suleman is
suitable.

Firstly what result does it give with the input values of 23.00 and 2.00?
Did the OP want 180, or your result of -1260?

Secondly, how many minutes do you get if the inputs are 9.00 and 9.20? Your
formula gives 12, wheras the answer given for an input of 9:00 and 9:20 with
the formulae which I and other contributors have suggested would be 20
minutes. Which answer do you think the OP wants?

 

[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