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(A1>B1,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 <Mcsp...@discussions.microsoft.com> 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(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


"Mcspore" <(E-Mail Removed)> wrote in message
news:2D927AC5-32E7-438D-A893-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> =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
>
>
> "Mcspore" <(E-Mail Removed)> wrote in message
> news:2D927AC5-32E7-438D-A893-(E-Mail Removed)...
>>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
news:(E-Mail Removed)...
> ... or (somewhat shorter) =MOD(K1-J1,1)*24*60
> --
> David Biddulph
>
> "Bill Kuunders" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> =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
>>
>>
>> "Mcspore" <(E-Mail Removed)> wrote in message
>> news:2D927AC5-32E7-438D-A893-(E-Mail Removed)...
>>>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+(J1>K1))*24*60
which works because the boolean TRUE or FALSE from (J1>K1) is evaluated as 1
or 0.
--
David Biddulph

"Bill Kuunders" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks David,
>
> So why does it work?
> Never seen this one before.
>
> Bill K
> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
> news:(E-Mail Removed)...
>> ... or (somewhat shorter) =MOD(K1-J1,1)*24*60
>> --
>> David Biddulph
>>
>> "Bill Kuunders" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>>
>>> =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
>>>
>>>
>>> "Mcspore" <(E-Mail Removed)> wrote in message
>>> news:2D927AC5-32E7-438D-A893-(E-Mail Removed)...
>>>>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
news:LpqdnRqHytJGX8banZ2dnUVZ8h-(E-Mail Removed)...
> 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.
> --
> David Biddulph
>
> "Bill Kuunders" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks David,
>>
>> So why does it work?
>> Never seen this one before.
>>
>> Bill K
>> "David Biddulph" <groups [at] biddulph.org.uk> wrote in message
>> news:(E-Mail Removed)...
>>> ... or (somewhat shorter) =MOD(K1-J1,1)*24*60
>>> --
>>> David Biddulph
>>>
>>> "Bill Kuunders" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>>
>>>> =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
>>>>
>>>>
>>>> "Mcspore" <(E-Mail Removed)> wrote in message
>>>> news:2D927AC5-32E7-438D-A893-(E-Mail Removed)...
>>>>>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.
>>>>
>>>>
>>>
>>>
>>
>>
>
>