How do I get time elapsed in terms of minute?

[Mcspore]

I cannot, for the life of me, figure out how to make a cell read time in
total minutes. I am trying to read the amount of time elapsed in minutes.
For example, I start running a sample at 18:32 (24 hour clock) and stops at
21:43. I can get Excel to calculate the difference to 3:11 (3 hours and 11
mins) but I need it in terms of minutes (191 mins). I also cannot find out
how to get excel to calculate time elapsed of any kind if it changes days.
For example, if I start a sample at 22:32 and it ends at 01:43 (once again
191 mins) excel gets confused.

 

[pseudo_pro]

Try this:
=((A2-A1)*60)*24

A2 is the date/time for end and A1 is date/time for beginning

 

[Pete_UK]

Assuming start time is in A1 and finish time in B1. You need to know
that Excel stores times as fractions of a 24-hour day, so 12 hours is
stored internally as 0.5, 6 hours as 0.25. Thus, any answer you get
which is in Excel time format needs to be multiplied by 24 and by 60
(and the cell formatted as number) if you want it to be in minutes.

Further, in the situation where the start time appears to be larger
than the finish time because you have gone through midnight, you need
to add 1 to any subtraction of the times to account for this. So, this
leads to (one of many ways of doing it):

=IF(A1>B1,B1-A1+1,B1-A1)*24*60

Format the cell as General or as Number.

Hope this helps.

Pete

 

[Bill Kuunders]

=IF(J1>K1,(K1+1-J1)*24*60,(K1-J1)*24*60)

J1 is start time
K1 is end time

formatted as general

normally a general format time result gives you part of a day
thats why you have to multiply by 24 and 60 to get minutes
the "if" formula part will ensure you get the right result when times fall
passed midnight


Bill K
Greetings from New Zealand

 

[FloMM2]

Mcspore,
Try this:
Column B Column C
Start End
Lapse
54 12/8/07 18:32 12/8/07 21:43
=(((C54-B54)*86400)/60)
55 12/8/07 22:32 12/9/07 1:43
=(((C55-B55)*86400)/60)
Both these give you 191 minutes.
Make sure format cell B54, C54, and all others as Date and Time.
HTH
Dennis

 

[FloMM2]

Mcspore<
Or you could try this:
Start
A1
End
B1
Lapse
C1

A1=Date and time
B1=Date and Time
C1=((B1-A1)*1440)

hth

 

[David Biddulph]

.... or (somewhat shorter) =MOD(K1-J1,1)*24*60

 

[Bill Kuunders]

Thanks David,

So why does it work?
Never seen this one before.

Bill K

 

[David Biddulph]

From -18:00, the formula =MOD(-0.75,1) will return 0.25 which is 06:00.

The other useful simplification of your formula is =(K1-J1+(J1>K1))*24*60
which works because the boolean TRUE or FALSE from (J1>K1) is evaluated as 1
or 0.

 

[Bill Kuunders]

Thanks a lot David

From -18:00, the formula =MOD(-0.75,1) will return 0.25 which is 06:00.

The other useful simplification of your formula is =(K1-J1+(J1>K1))*24*60
which works because the boolean TRUE or FALSE from (J1>K1) is evaluated as
1 or 0.