SOLVED
Home

Calculating with Time

%3CLINGO-SUB%20id%3D%22lingo-sub-291216%22%20slang%3D%22en-US%22%3ECalculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291216%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20have%20a%20reducing%20total%20on%20a%20time%20calculation%20e.g.%2010000%3A00%20less%203000%3A00%20%3D%207000%3A00.%20I%20have%20formated%20the%20Hours%20cell%20%5Bh%5D%3A00%20which%20works%2C%20but%20when%20I%20input%2010000%3A00%20it%20stops%20working.%20The%20input%20cells%20are%20at%20O6%20%26amp%3B%20P6%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-291216%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291248%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291248%22%20slang%3D%22en-US%22%3E%3CP%3EJohn%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291245%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291245%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EJust%20figured%20out%20your%20answer%20which%20works.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291240%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291240%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20from%20scratch%20-%20in%20Excel%20dates%20are%20sequential%20integer%20numbers%20starting%20from%20Jan%2001%2C%201900%2C%20that%20date%20is%20equal%20to%201.%20Thus%20the%20date%20Nov%2026%2C%202018%20is%20equal%20to%20number%204340.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETime%20is%20decimal%20part%20of%20the%20number%2C%20since%20it's%2024%20hours%20in%20the%20day%2C%20one%20hour%20is%20equal%20to%201%2F24.%20Thus%206pm%20at%20Nov%2026%2C%202018%20is%20equal%20to%204340.75%20(4340%2B18%2F24).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20rest%20is%20only%20formatting%20to%20present%20date%2Ftime%20in%20human%20friendly%20form.%20Using%20%5Bhh%5D%3Amm%20format%20you%20present%20elapsed%20time%20without%20splitting%20it%20on%20days.%20If%20you%20use%20that%20format%20and%20formulas%20as%20here%20(in%20second%20row%20are%20formulas%20which%20are%20in%20first%20one)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20252px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F60679iE399C0062BCDE293%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eyou%20may%20avoid%209999%3A99%20limit%20for%20manual%20entry%20of%20the%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291229%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291229%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei%2C%3C%2FP%3E%3CP%3EThanks%20for%20that.%20I'm%20more%20of%20a%20user%20thanTechnical%2C%20so%20could%20you%20expline%20in%20some%20more%20detail%20what%20I%20need%20to%20do.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EJohn%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-291223%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20with%20Time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-291223%22%20slang%3D%22en-US%22%3E%3CP%3EThat's%20Excel%20limitation%2C%20you%20can't%20enter%20manually%20the%20time%20more%20than%209999%3A99%3A99.%20For%20greater%20values%20you%20shall%20enter%20as%20date%2Ftime%20(e.g.%26nbsp%3B1901-02-19%26nbsp%3B%2016%3A00%3A00%20to%20receive%2010000%3A00)%20or%20use%20formulas%20(%3D10000%2F24).%3C%2FP%3E%3C%2FLINGO-BODY%3E
jpad-2018
New 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

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

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies