Number to time conversion, or lookup?

[sparkyrick]

I'm trying to automate the data entered into my time card. If I have the number 10 in cell B15, sheet two, I would like 4:00PM automatically entered in cell E5, sheet one. And the data in B15, sheet two, would have varied data with a range like 10=4:00PM, 9.5=3:30PM, 9=3:00pm, 8.5=2:30PM, 8=2:00PM, ect...Any ideas?

 

[Oaktree]

Welcome to the board.

Try E5 =TIMEVALUE("6:00")+Sheet2!B15/24

Format E5 as h:mm AM/PM

 

[colbymack]

This will work for a cell formatted to "general". I believe the prior post requires a cell to be formatted to time (depends on your needs).

=(TIME(6+(B15),0+(-60*(+B15-(ROUND(B15,0.1)))),0))

 

[colbymack]

sorry - I missed that Oaktree had already specified to format as time.

 

[sparkyrick]

ADVERTISEMENT

It works!

Hey Oaktree, Thanks for the welcome and thanks for your answer to my question. I don't know what that formula is doing, but it works exactly like I wanted. Thanks again.

 

[Oaktree]

Excel calculates time as fractions of a day. Since your example matches 10 to 4:00PM, the 10 represents 10 hours after 6:00AM.

So, the formula starts with 6:00AM, then adds 10 hours (10/24 of a day) to that value.

HTH.

 

[sparkyrick]

I see now. Thanks for the explanation.

-Rick