Forum Discussion
Calculating with Time
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
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)
you may avoid 9999:99 limit for manual entry of the time.
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
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)
you may avoid 9999:99 limit for manual entry of the time.
