SOLVED
Home

Converting time to a digital number

%3CLINGO-SUB%20id%3D%22lingo-sub-675910%22%20slang%3D%22en-US%22%3EConverting%20time%20to%20a%20digital%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675910%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%20I%20used%20to%20use%20MS%20Office%20in%20the%2080s%20and%2090s%20and%20now%20I'm%20getting%20started%20with%20365%2C%20doing%20freelance%20work.%20I'm%20making%20a%20spreadsheet%20to%20calculate%20charges%20for%20my%20work%2C%20and%20have%20gotten%20a%20bit%20confused%2C%20after%20using%20what%20I%20thought%20was%20the%20correct%20format%20seen%20in%20another%20post%20on%20this%20forum.%20The%20formula%20I'm%20using%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(TotalTime-INT(TotalTime))*24%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eto%20take%20the%20total%20time%20worked%20(1%3A10)%2C%20but%20the%20field%20shows%204%3A00%20instead%20of%201.67.%20What%20did%20I%20miss%2C%20to%20get%20this%20erroneous%20data%3F%20(The%20Help%20on%20this%20subject%20got%20me%20thoroughly%20confused.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3ERache%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-675910%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675942%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20time%20to%20a%20digital%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675942%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356057%22%20target%3D%22_blank%22%3E%40RachelHoff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20easiest%20way%20to%20convert%20time%20to%20decimal%20in%20Excel%20is%20to%20multiply%20the%20original%20time%20value%20by%20the%20number%20of%20hours%2C%20seconds%20or%20minutes%20in%20a%20day%3A%3C%2FP%3E%3CUL%3E%3CLI%3ETo%20convert%20time%20to%20a%20number%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Ehours%3C%2FSTRONG%3E%2C%20multiply%20the%20time%20by%2024%2C%20which%20is%20the%20number%20of%20hours%20in%20a%20day.%3C%2FLI%3E%3CLI%3ETo%20convert%20time%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Eminutes%3C%2FSTRONG%3E%2C%20multiply%20the%20time%20by%201440%2C%20which%20is%20the%20number%20of%20minutes%20in%20a%20day%20(24*60).%3C%2FLI%3E%3CLI%3ETo%20convert%20time%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3Eseconds%3C%2FSTRONG%3E%2C%20multiply%20the%20time%20time%20by%2086400%2C%20which%20is%20the%20number%20of%20seconds%20in%20a%20day%20(24*60*60%20).%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20332px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117376i3CAFE17AD331DEED%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22convert-time-decimal-excel%22%20title%3D%22convert-time-decimal-excel%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EAkshay%3C%2FP%3E%3CP%3ETech%20support%20%7C%20%3CA%20href%3D%22https%3A%2F%2Fwww.o365cloudexperts.com%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EO365CloudExperts%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-675950%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20time%20to%20a%20digital%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-675950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356057%22%20target%3D%22_blank%22%3E%40RachelHoff%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20of%20the%20formula%20is%20correct%2C%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ebut%20it%20appears%20this%20way%20because%20of%20cell%20formatting%20(%3C%2FSPAN%3E%3C%2FSPAN%3ETime%20format).%3C%2FP%3E%3CP%3EYou%20need%20to%20switch%20to%20General%20or%20Number%20format%20like%20the%20below%20screenshot%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F117377iCBFC299B6E4E0CC5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Switch%20to%20General%20Format.png%22%20title%3D%22Switch%20to%20General%20Format.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20you%20can%20apply%20the%20format%20in%20the%20formula%20as%20follows%3A%3C%2FP%3E%3CPRE%3E%3DTEXT((A1-INT(A1))*24%2C%220.00%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20the%20mention%20that%20the%20formula%20can%20be%20simplified%20like%20this%3A%3C%2FP%3E%3CPRE%3E%3DTEXT(A1*24%2C%220.00%22)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678677%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20time%20to%20a%20digital%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678677%22%20slang%3D%22en-US%22%3EThank%20you!%20I%20suspected%20it%20had%20been%20something%20simple%2C%20since%20I%20used%20an%20answer%20you%20yourself%20gave%20another%20person!%20%3A)%3C%2FLINGO-BODY%3E
RachelHoff
New Contributor

Hello. I used to use MS Office in the 80s and 90s and now I'm getting started with 365, doing freelance work. I'm making a spreadsheet to calculate charges for my work, and have gotten a bit confused, after using what I thought was the correct format seen in another post on this forum. The formula I'm using is:

 

=(TotalTime-INT(TotalTime))*24

 

to take the total time worked (1:10), but the field shows 4:00 instead of 1.67. What did I miss, to get this erroneous data? (The Help on this subject got me thoroughly confused.)

 

Thanks in advance,

Rache

3 Replies

@RachelHoff 

 

The easiest way to convert time to decimal in Excel is to multiply the original time value by the number of hours, seconds or minutes in a day:

  • To convert time to a number of hours, multiply the time by 24, which is the number of hours in a day.
  • To convert time to minutes, multiply the time by 1440, which is the number of minutes in a day (24*60).
  • To convert time to seconds, multiply the time time by 86400, which is the number of seconds in a day (24*60*60 ).

convert-time-decimal-excel

 

Regards,

Akshay

Tech support | O365CloudExperts

Solution

@RachelHoff

 

Hi,

 

The result of the formula is correct, but it appears this way because of cell formatting (Time format).

You need to switch to General or Number format like the below screenshot:

Switch to General Format.png

 

Also, you can apply the format in the formula as follows:

=TEXT((A1-INT(A1))*24,"0.00")

 

I'd like the mention that the formula can be simplified like this:

=TEXT(A1*24,"0.00")

 

Hope that helps

Thank you! I suspected it had been something simple, since I used an answer you yourself gave another person! :)
Related Conversations