Nov 26 2018 09:29 AM
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
Nov 26 2018 09:43 AM - edited Nov 26 2018 09:48 AM
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).
Nov 26 2018 09:51 AM
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
Nov 26 2018 10:06 AM
SolutionHi 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.
Nov 26 2018 10:20 AM
Hi Sergei,
Just figured out your answer which works.
Thanks
John
Nov 26 2018 10:06 AM
SolutionHi 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.