SOLVED
Home

Time in digital (for large numbers)

%3CLINGO-SUB%20id%3D%22lingo-sub-469122%22%20slang%3D%22en-US%22%3ETime%20in%20digital%20(for%20large%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469122%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20...%20my%20first%20post%20here.%26nbsp%3B%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20a%20freelancer%2C%20I%20use%20Excel%20to%20track%20the%20hours%20I%20work%20on%20a%20particular%20job.%26nbsp%3B%20I%20then%20convert%20the%20total%20time%20worked%20(say%20%3CEM%3E2%3A45%3C%2FEM%3E%26nbsp%3Bwhich%20is%20formatted%20as%26nbsp%3B%3CEM%3Eh%3Amm%3C%2FEM%3E)%20into%20decimal%20so%20I%20can%20multiply%20it%20by%20my%20hourly%20rate.%26nbsp%3B%20I%20use%20the%20formula%20%3CEM%3E%22%3D(A1-INT(A1))*24%22%3C%2FEM%3E%2C%20so%20for%202%3A45%20I'd%20get%20%3CEM%3E2.8%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20good%20so%20far.%26nbsp%3B%20But%20for%20a%20job%20of%20many%20days%2C%20let's%20say%20I%20worked%20a%20total%20time%20of%2069hrs%2055min.%26nbsp%3B%20For%20reasons%20I%20don't%20quite%20understand%2C%20I%20need%20to%20change%20the%20cell%20format%20slightly%20to%20'elapsed'%20time%20%3CEM%3E(%3C%2FEM%3E%3CEM%3E%5Bh%5D%3Amm)%3C%2FEM%3E%20to%20get%20%3CEM%3E69%3A55%3C%2FEM%3E%20to%20appear.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20I%20go%20to%20convert%20these%20larger%20numbers%20into%20digital%2C%20it%20won't%20work.%26nbsp%3B%20The%20formula%20mentioned%20above%20gives%20me%20a%20crazy%20result%20%3CEM%3E(526%3A00%3A00)%3C%2FEM%3E.%26nbsp%3B%20I%20found%20some%20other%20suggestions%20online%2C%20but%20none%20worked.%26nbsp%3B%20It%20seems%20that%20using%20elapsed%20time%20is%20throwing%20a%20spanner%20in%20the%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20have%20a%20suggestion%3F%3C%2FP%3E%3CP%3EMany%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-469122%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469219%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20in%20digital%20(for%20large%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469219%22%20slang%3D%22en-US%22%3ESimply%20multiply%20by%2024.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469569%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20in%20digital%20(for%20large%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469569%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20worked.%26nbsp%3B%20I%20cannot%20believe%20it%20was%20that%20simple.%26nbsp%3B%20*slaps%20head*%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-469642%22%20slang%3D%22en-US%22%3ERe%3A%20Time%20in%20digital%20(for%20large%20numbers)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-469642%22%20slang%3D%22en-US%22%3EAlways%20remember%20that%20hours%20are%20stored%20as%20decimals%20of%201%20day%2C%20which%20is%20equivalent%20to%2024%20hours.%3C%2FLINGO-BODY%3E
David-Budhawa1
New Contributor

Hello ... my first post here.  :)

 

As a freelancer, I use Excel to track the hours I work on a particular job.  I then convert the total time worked (say 2:45 which is formatted as h:mm) into decimal so I can multiply it by my hourly rate.  I use the formula "=(A1-INT(A1))*24", so for 2:45 I'd get 2.8.

 

All good so far.  But for a job of many days, let's say I worked a total time of 69hrs 55min.  For reasons I don't quite understand, I need to change the cell format slightly to 'elapsed' time ([h]:mm) to get 69:55 to appear.

 

But when I go to convert these larger numbers into digital, it won't work.  The formula mentioned above gives me a crazy result (526:00:00).  I found some other suggestions online, but none worked.  It seems that using elapsed time is throwing a spanner in the works.

 

Anyone have a suggestion?

Many thanks!

3 Replies
Simply multiply by 24.

It worked.  I cannot believe it was that simple.  *slaps head*


Solution
Always remember that hours are stored as decimals of 1 day, which is equivalent to 24 hours.
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies