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 ce...

Technical Log.xlsx22 KB

excel

SergeiBaklan
Nov 26, 2018
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.

jpad-2018

Copper Contributor

Nov 26, 2018

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

SergeiBaklan

Diamond Contributor

Nov 26, 2018

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.

Forum Discussion

Calculating with Time

Resources