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?

 

[Justinian]

I left 2.47 in there to show you what I was doing before today.

I changed to your formula but my result is 216:21 instead of 208:00. I am trying to use MrExcel but it keeps crashing Excel when I try to generate output.

 

[Justinian]

Attendance Cards - 2021.xlsm
	AI	AJ	AK
3	208:00
4
5	Used PTO
6	0:00
7	8:00
8
9
10	0:00
11
12	0:00
13
14	0:00
15
16	0:00
17	0:00
18	0.333333
19	Remaining Package Balance
20	PTO
21	208:21
Cruz, Renilio

Cell Formulas
Range	Formula
AI3	AI3	=208/24
AI6:AI17	AI6	=IFERROR(IF(COUNTA(C6:AG6)=0,"",IF(COUNTIF(C6:AG6,"?*"),SUM(IF(LEFT(C6:AG6&"|",4)="PTO|",8,IF(1-(C6:AG6=""),IF(LEFT(C6:AG6,3)="PTO",REPLACE(C6:AG6,1,3,"")+0))))))/24,"")
AI18	AI18	=SUM(AI6:AI17)
AI21	AI21	=AI3-SUM(AI6:AK17)+INT((TODAY()-DATE(2021,1,4-7))/7)*"2:47"

 

[jasonb75]

I'm not seeing how you're arriving at the expected result. You have 208 at the top, but then you're adding and subtracting bits which are changing that value at the bottom.

Can you post an example with the correct results using your original method of decimal times in all cells? e.g. 208 in the top cell rather than 208/24.

 

[Justinian]

Sorry, here is my original data using decimals:

Attendance Cards - 2021.xlsm
	AI	AJ	AK
1	1/1/21 Starting Balance
2	PTO
3	362
4
5	Used PTO
6	8
7
8
9
10	0
11
12	0
13
14	0
15
16	0
17	0
18	8
19	Remaining Package Balance
20	PTO
21	364.5
Yoro, Severino

Cell Formulas
Range	Formula
AI6:AI17	AI6	=IF(COUNTA(C6:AG6)=0,"",IF(COUNTIF(C6:AG6,"?*"),SUM(IF(LEFT(C6:AG6&"|",4)="PTO|",8,IF(1-(C6:AG6=""),IF(LEFT(C6:AG6,3)="PTO",REPLACE(C6:AG6,1,3,"")+0))))))
AI18	AI18	=SUM(AI6:AI17)
AI21	AI21	=AI3-SUM(AI6:AK17)+INT((TODAY()-DATE(2021,1,1-7))/7)*3.5
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

I had a bit of a brainfart in my earlier suggestion, 2.47 and "2:47" are not the same thing, not entirely sure what I was thinking there, or even if I was thinking at all.
Based on post 14, see if this helps

Book1(AutoRecovered)2.xlsm
	AI	AJ	AK
3	362:00
4
5	Used PTO
6	0:00
7	8:00
8
9
10	0:00
11
12	0:00
13
14	0:00
15
16	0:00
17	0:00
18	8:00
19	Remaining Package Balance
20	PTO
21	364:30
Sheet4

Cell Formulas
Range	Formula
AI3	AI3	=362/24
AI18	AI18	=SUM(AI6:AI17)
AI21	AI21	=AI3-SUM(AI6:AK17)+INT((TODAY()-DATE(2021,1,1-7))/7)*(3.5/24)

 

[Justinian]

Ok, I get it now.

Thank you both for your help!