CODEWITHSUNDEEP
excelvalidation
mFranck
mFranck

Reputation: 27

Change decimal number directly into hour in Excel

I need to convert lots of numbers that represent hours, into actual Excel values formatted like hours.

In e.g., I have a cell that has the value "16.30", and I need it to be "16:30".

I tried replacing the dot with two dots, formatting the cells with a custom format like "00:00" and "hh:mm", but nothing works. Excel returns an error or changes the value of the hour by converting the numeric value into an hour, as usual.

Any ideas about how to achieve the goal listed above?

TL;DR: how to change a cell with a value "16.30" into "16:30" as an hour?

Thanks in advance

Upvotes: 0

Views: 217

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

You can use this:

=TIME(INT(H6),(H6-INT(H6))*100,0)

Where H6 is your cell. Then format it as you want.

Upvotes: 2

John Coleman
John Coleman

Reputation: 52008

One way to make your idea of substituting ":" for "." work is like this:

=TIMEVALUE(SUBSTITUTE(TEXT(A1,"0.00"),".",":"))

if A1 contains 16.3 and A2 contains the above and is formatted as time then it will display as time.

Upvotes: 2

Related Questions