convert numbers to time

%3CLINGO-SUB%20id%3D%22lingo-sub-2190454%22%20slang%3D%22en-US%22%3Econvert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190454%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20trying%20to%20convert%20a%20number%20format%20in%20one%20cell%20to%20time%20format%20in%20another%20cell.%26nbsp%3B%20Here%20is%20my%20example%3C%2FP%3E%3CP%3EIn%20cell%20A1%20I%20have%20a%20number%201.90%20and%20I%20need%20it%20to%20be%20converted%20into%201%3A90%20in%20cell%20B1.%20what%20formula%20do%20I%20use.%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2190454%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-2190516%22%20slang%3D%22en-US%22%3ERe%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190516%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F989192%22%20target%3D%22_blank%22%3E%40sliao8788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%201%3A90%20shall%20practically%20mean%2C%201hour%2090%20minutes%3F%20Or%20you%20mean%201.9%2F24%20%3D%2001%3A54%3A00%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190545%22%20slang%3D%22en-US%22%3ERe%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Byes%20it%20should%20show%20%3CSPAN%3E1hour%2090%20minutes%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190609%22%20slang%3D%22de-DE%22%3ESubject%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190609%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F989192%22%20target%3D%22_blank%22%3E%40sliao8788%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20permission%2C%20everyone%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AIAIAIAI.JPG%22%20style%3D%22width%3A%20691px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261409iE2E8AFDDFCEC9503%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22AIAIAIAI.JPG%22%20alt%3D%22AIAIAIAI.%20Jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190638%22%20slang%3D%22en-US%22%3EBetreff%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190638%22%20slang%3D%22en-US%22%3EIf%20I%20do%20this%20it%20changes%20the%20value%20to%2021%3A36%20and%20not%20leave%20it%20as%201%3A90%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190670%22%20slang%3D%22de-DE%22%3ESubject%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190670%22%20slang%3D%22de-DE%22%3EYou%20are%20absolutely%20right%2C%20what%20too%20hasty%20...%20I%20have%20to%20give%20me%20more%20time%20to%20let%20everything%20through.%3CBR%20%2F%3EFile%26gt%3B%20Options%26gt%3B%20Document%20Review%26gt%3B%20%3CBR%20%2F%3E%20For%20this%20purpose%20you%20could%20create%20an%20entry%20in%20the%20autocorrection%3A%20%3CBR%20%2F%3E%20Replace%3A%2C%20(comma)%20%3CBR%20%2F%3E%20through%3A%3A%20(colon)%20%3CBR%20%2F%3E%20It%20is%20now%20possible%20to%20enter%20a%20time%20in%20the%20following%20format%3A%2010%2C%2C%2020%2C%2C%2030%20and%20this%20automatically%20becomes%2010%3A20%3A30%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190753%22%20slang%3D%22en-US%22%3EBetreff%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190753%22%20slang%3D%22en-US%22%3EThanks%2C%20This%20kind%20of%20solves%20the%20problem.%20but%20this%20was%20what%20I%20was%20trying%20to%20do%2C%3CBR%20%2F%3EIn%20cell%20A1%20I%20have%20a%20number%201.90%20and%20I%20need%20it%20to%20be%20converted%20into%201%3A90%20in%20cell%20B1.%20what%20formula%20do%20I%20use.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190777%22%20slang%3D%22de-DE%22%3ESubject%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190777%22%20slang%3D%22de-DE%22%3E%3CP%3EFormula%20from%20excel%202010%20%3CBR%20%2F%3E%20%3DSUBSTITUTE(A1%2C%22%22%2C%22%2C%22%2C%22)*1%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20This%20formula%20was%20available%20from%20Excel%2097%20%3CBR%20%2F%3E%20%3DREPLACE(A1%2CFIND(%22.%22%2CA1)%2C1%2C%22%3A%22)*1%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Thank%20you%20for%20your%20understanding%20and%20patience%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20I%20know%20I%20%3CBR%20%2F%3E%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190778%22%20slang%3D%22en-US%22%3EBetreff%3A%20convert%20numbers%20to%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190778%22%20slang%3D%22en-US%22%3EIf%20you%20just%20want%20it%20to%20display%20%221%3A90%22%2C%20then%20you%20could%20try%3A%3CBR%20%2F%3E%3DSUBSTITUTE(TEXT(A1%2C%220.00%22)%2C%22.%22%2C%22%3A%22)%3CBR%20%2F%3E%3CBR%20%2F%3EBut%2C%20it%20will%20be%20a%20text%20value%2C%20so%20you%20won't%20be%20able%20to%20perform%20any%20arithmetic%20operations%20with%20it%20very%20easily.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20believe%20you%20could%20use%3A%3CBR%20%2F%3E%3DTIMEVALUE(SUBSTITUTE(TEXT(A1%2C%220.00%22)%2C%22.%22%2C%22%3A%22))%3CBR%20%2F%3E%3CBR%20%2F%3Eand%20format%20it%20as%20%5Bh%5D%3Amm%20and%20it%20would%20be%20an%20actual%20numeric%20time%20value%2C%20but%20it%20will%20display%20as%20%222%3A30%22%20and%20not%20%221%3A90%22.%20If%20you%20want%20it%20to%20display%20as%20hours%3Aminutes%2C%20then%20I'm%20not%20sure%20how%20you%20stop%20excel%20from%20incrementing%20the%20hours%20when%20you%20go%20past%2060.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi I am trying to convert a number format in one cell to time format in another cell.  Here is my example

In cell A1 I have a number 1.90 and I need it to be converted into 1:90 in cell B1. what formula do I use.

Thanks

16 Replies

@sliao8788 

What 1:90 shall practically mean, 1hour 90 minutes? Or you mean 1.9/24 = 01:54:00 ?

@Sergei Baklan yes it should show 1hour 90 minutes

 

@sliao8788 

With permission, everyone

AIAIAIAI.JPG

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

If I do this it changes the value to 21:36 and not leave it as 1:90
You are absolutely right, was too hasty ... I have to give me more time to let everything through.
File> Options> Document Review>
For this purpose you could create an entry in the autocorrection:
Replace:, (comma)
through:: (colon)
It is now possible to enter a time in the following format: 10,, 20,, 30 and this automatically becomes 10:20:30


Nikolino
I know I don't know anything (Socrates)
Thanks, This kind of solves the problem. but this was what I was trying to do,
In cell A1 I have a number 1.90 and I need it to be converted into 1:90 in cell B1. what formula do I use.

Formula from excel 2010
=SUBSTITUTE(A1,".",",")*1

This formula was available from Excel 97
=REPLACE(A1,FIND(".",A1),1,":")*1

Thank you for your understanding and patience


Nikolino
I know I don't know anything (Socrates)

If you just want it to display "1:90", then you could try:
=SUBSTITUTE(TEXT(A1,"0.00"),".",":")

But, it will be a text value, so you won't be able to perform any arithmetic operations with it very easily.

I believe you could use:
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"0.00"),".",":"))

and format it as [h]:mm and it would be an actual numeric time value, but it will display as "2:30" and not "1:90". If you want it to display as hours:minutes, then I'm not sure how you stop excel from incrementing the hours when you go past 60.

@Nikolino  Thanks for all the help. Tried this method and it is giving a value of 1:09 and not 1:90. if I change the number to 1.91 in cell A1, it gives me a result of 2:31

@JMB17 Thanks for the help. I need it in time value as I will be adding it up.

Then, I believe the second option will work.
=TIMEVALUE(SUBSTITUTE(TEXT(A1,"0.00"),".",":"))

Which is the same as what Nikolino is suggesting, but formatting the number to 2 decimals before replacing the "." with ":" as it appears excel interprets "1:9" as 1 hr 9 minutes. So, it seems you have to force the two decimals to get it to see it as 90 minutes.

Numerically, 2:30 is the same as 1:90, so it should make no difference for your calculations.

@sliao8788 For what it is worth, here's a numerical variant. That is, without text manipulation.

=INT(A1)/24+MOD(A1,1)*100/1440

formatted as time "h:mm". As in @JMB17 's solution, it will return 2:30 (2 hrs and 30 minutes).

Screenshot 2021-03-07 at 08.02.42.png

@JMB17, Thanks for the response. This seems to be working. Appreciate your help. Now I have come up with another problem. Eg: Cell A1, if I have a number that is 26.00 with the formula in cell B1, it returns with 2:00 and not 26:00. Is there a way to make excel recognise this number as I know the time in excel is 24 hrs period. Also thanks to @Nikolino and @Riny_van_Eekelen for you help.

Try changing the number format to: [h]:mm

@JMB17 Thanks for the response.  When I tried it with your formula, it didn't work even with changing the format to [H]:mm, but when I tried it with the formula @Riny_van_Eekelen  suggested, it worked.  

 

Sincere thanks to @JMB17 @Riny_van_Eekelen  and @Nikolino  for all your help.  Appreciate it.  

@sliao8788 

Here again a file with the example from 1.9 to 1:90.

One would only have to format 1.9 as text.

But if you want it to remain time and to be seen as time then here is a link and a formula.

 

Insert colon between numbers to make them as time format with formulas

=TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),0)

 

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)