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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies