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.

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

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

"Mcspore" wrote:

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.

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(A1B1,B1-A1+1,B1-A1)*24*60

Format the cell as General or as Number.

Hope this helps.

Pete

On Dec 8, 8:38 pm, Mcspore wrote:
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.

=IF(J1K1,(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


"Mcspore" wrote in message
...
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.

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

"Mcspore" wrote:

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.

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

"Mcspore" wrote:

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.

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

"Bill Kuunders" wrote in message
...

=IF(J1K1,(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


"Mcspore" wrote in message
...
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.

Thanks David,

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

Bill K
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
... or (somewhat shorter) =MOD(K1-J1,1)*24*60
--
David Biddulph

"Bill Kuunders" wrote in message
...

=IF(J1K1,(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


"Mcspore" wrote in message
...
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.

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+(J1K1))*24*60
which works because the boolean TRUE or FALSE from (J1K1) is evaluated as 1
or 0.
--
David Biddulph

"Bill Kuunders" wrote in message
...
Thanks David,

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

Bill K
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
... or (somewhat shorter) =MOD(K1-J1,1)*24*60
--
David Biddulph

"Bill Kuunders" wrote in message
...

=IF(J1K1,(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


"Mcspore" wrote in message
...
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.

Thanks a lot David

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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+(J1K1))*24*60
which works because the boolean TRUE or FALSE from (J1K1) is evaluated as
1 or 0.
--
David Biddulph

"Bill Kuunders" wrote in message
...
Thanks David,

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

Bill K
"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
... or (somewhat shorter) =MOD(K1-J1,1)*24*60
--
David Biddulph

"Bill Kuunders" wrote in message
...

=IF(J1K1,(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


"Mcspore" wrote in message
...
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.