SOLVED

Calculating with Time

Copper Contributor

I want to have a reducing total on a time calculation e.g. 10000:00 less 3000:00 = 7000:00. I have formated the Hours cell [h]:00 which works, but when I input 10000:00 it stops working. The input cells are at O6 & P6

Any ideas?

John

5 Replies

That's Excel limitation, you can't enter manually the time more than 9999:99:99. For greater values you shall enter as date/time (e.g. 1901-02-19  16:00:00 to receive 10000:00) or use formulas (=10000/24).

Hi Sergei,

Thanks for that. I'm more of a user thanTechnical, so could you expline in some more detail what I need to do.

Thanks

John

best response confirmed by jpad-2018 (Copper Contributor)
Solution

Hi John,

 

If from scratch - in Excel dates are sequential integer numbers starting from Jan 01, 1900, that date is equal to 1. Thus the date Nov 26, 2018 is equal to number 4340.

 

Time is decimal part of the number, since it's 24 hours in the day, one hour is equal to 1/24. Thus 6pm at Nov 26, 2018 is equal to 4340.75 (4340+18/24).

 

The rest is only formatting to present date/time in human friendly form. Using [hh]:mm format you present elapsed time without splitting it on days. If you use that format and formulas as here (in second row are formulas which are in first one)

image.png

you may avoid 9999:99 limit for manual entry of the time.

Hi Sergei,

Just figured out your answer which works.

Thanks

John

John, you are welcome

1 best response

Accepted Solutions
best response confirmed by jpad-2018 (Copper Contributor)
Solution

Hi John,

 

If from scratch - in Excel dates are sequential integer numbers starting from Jan 01, 1900, that date is equal to 1. Thus the date Nov 26, 2018 is equal to number 4340.

 

Time is decimal part of the number, since it's 24 hours in the day, one hour is equal to 1/24. Thus 6pm at Nov 26, 2018 is equal to 4340.75 (4340+18/24).

 

The rest is only formatting to present date/time in human friendly form. Using [hh]:mm format you present elapsed time without splitting it on days. If you use that format and formulas as here (in second row are formulas which are in first one)

image.png

you may avoid 9999:99 limit for manual entry of the time.

View solution in original post