Cumulative Time Problem calculating more than 24 hours.

[J]

Hi,

I need to create a spreadsheet that can convert time into hours, the inbuilt
functions only return times within a 24hour period which is completely
useless to me. I need to give a start date & time, i.e

24 August 2004 09:15

and an end date/time

24 August 2005 11:23

and have it return the total running time in a variety of formats, including
plain simple total hours (i.e. one years worth plus a couple).

Any ideas how to do this. I want to be able to keep a running total, as well
but I can probably figure it out once I can get the calculation for the
total (greater than 24 hours) hours working.

Regards

J

 

[Guest]

"Inbuilt function" provides days to the left of the decimal point and
specific time to the right of the decimal

Format your cell for time instead of date

try:
=minute(your date serial number here)

=minute(now())

 

[Niek Otten]

Calculation will be OK. Just format as [h]:mm to avoid tipping over at 24h

--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Gareth]

Excel handles date/times correctly (both in VBA and in worksheet
formulae) - at least most of the time..... What might be confusing is
how it stores them. See "About dates and date systems" and "Calculate
the difference between two times" in Excel Help for more info.

In your example, to get the number of hours, use the below formula:

A1 B1 C1
24 August 2004 09:15 24 August 2005 11:23 =INT((B22-A1)*24)

Gives me 8762 hours. Which is correct by my reckoning.

HTH,
Gareth