Home

changing time format in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-738077%22%20slang%3D%22en-US%22%3Echanging%20time%20format%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738077%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20change%20a%20time%20in%20Excel%20that%20looks%20like%20this%20%224%2C00%22%20(four%20hours)%20to%20this%3A%20%2204%3A00%22%20or%20%228%2C25%22%20hours%20to%20%2208%3A15%22.%20The%20first%20is%20with%20a%20comma%20(10%20number%20system)%20the%20second%20is%20with%20a%20colon%2C%20(60%20number%20system%20as%20we%20see%20it%20on%20digital%20clocks).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20function%20%22%3D(HOURS(A1)*60%2BMINUTES(A1))%2F60%22%20that%20converts%20it%20the%20other%20way%20(from%20%2208%3A15%22%20to%20%228%2C25%22)%20maybe%20I%20just%20need%20to%20change%20this%20somehow%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETam%C3%A1s%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-738077%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-738577%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20time%20format%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738577%22%20slang%3D%22en-US%22%3EHours%20are%20a%20fraction%20of%20a%20day%2C%20so%20divide%20your%20number%208%2C25%20by%2024%20and%20then%20format%20the%20cell%20as%20time.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738591%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20time%20format%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F371096%22%20target%3D%22_blank%22%3E%40tfulop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTIME(0%2CA1*60%2C0)%26nbsp%3B%20will%20convert%20decimal%20to%20time%20format.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738740%22%20slang%3D%22en-US%22%3ERe%3A%20changing%20time%20format%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738740%22%20slang%3D%22en-US%22%3EThanks%3C%2FLINGO-BODY%3E
tfulop
New Contributor

Hi all,

 

I would like to change a time in Excel that looks like this "4,00" (four hours) to this: "04:00" or "8,25" hours to "08:15". The first is with a comma (10 number system) the second is with a colon, (60 number system as we see it on digital clocks).

 

I have a function "=(HOURS(A1)*60+MINUTES(A1))/60" that converts it the other way (from "08:15" to "8,25") maybe I just need to change this somehow?

 

Thanks

 

Tamás

3 Replies
Hours are a fraction of a day, so divide your number 8,25 by 24 and then format the cell as time.

@tfulop 

=TIME(0,A1*60,0)  will convert decimal to time format. 

 

Thanks