Convert Decimal to Time

[Justinian]

I track paid time off using regular numbers (208.25) but we switched to ADP and now, we have to use time formats instead so 208.25 has to be come 208:15 and 208.75 has to become 208.45. I tried changing my cell formats to "h:mm" but that changes 208.25 to 6:00.

What am I doing wrong?

 

[Fluff]

How about

+Fluff v2.xlsm
	E	F
1
2	208.25	208:15
3	208.75	208:45
4	208.5	208:30
Main

Cell Formulas
Range	Formula
F2:F4	F2	=E2/24

and format to [h]:mm

 

[jasonb75]

In formula terms, 208.25 is evaluated as days, not hours .25 days is 6 hours.

To convert decimal time to proper, you need to divide by 24 and format the result as [h]:mm

 

[Justinian]

When I do that (208/24), 208 becomes 16:00.

 

[Fluff]

Did you format the cell as we said?

 

[jasonb75]

Did you change the format?

and format to [h]:mm

divide by 24 and format the result as [h]:mm

 

[Justinian]

I did that.

 

[jasonb75]

If it is showing as 16:00 then it implies that the square brackets are missing from the format. e.g. that you have used h:mm not [h]:mm

 

[Justinian]

Ok, I did not know I had to add the brackets.

It is working now but it is messing up my calculation cells. I am entering data into an attendance card so when someone takes a PTO day, I would enter 8 and this formula would calculate the employee's remaining time:

=AI3-SUM(AI6:AK17)+INT((TODAY()-DATE(2021,1,4-7))/7)*2.47

The formula worked when I used decimals but it will not work now that I have switched to time formatting. The purpose of the formula is to calculate the employee's remaining time and add 2:47 hours every Sunday as we earn 2:47 hours every week.

 

[jasonb75]

You still have 2.47 in the formula, not 2:47. Changing should solve the problem, but that depends on how AI3 and AI6:AK17 affect the calculations.

=AI3-SUM(AI6:AK17)+INT((TODAY()-DATE(2021,1,4-7))/7)*"2:47"

Note that in this instance the time is enclosed in double quotes.