I need help with creating a custom time format

[smllchng5]

So I am trying to create a custom cell format to show the Turn around time for a task. For example, someone spend 11 hours and 30 minutes on a task. I have the formula written to calculate the TAT, but it shows as 11:30 as the time it took, but I want that to show as 1.44 (or in in other words....11 hours spent in an 8 hours a day = 1 day and 3.5 hours ~ divded by 8 hours in a day is .4375...so final should be something like 1.44 (rounded up) I know it all has to do with the way the cell is formatted, but I am unclear what the proper format should be. Can you help me write the custom cell format please? Thanks in advance for your help.

 

[Asbestos_Jen]

If I understand correctly, I think you should just need to multiply 11:30 by 24 and divide by the length of shift (8 hours), and display with a normal number format:

Book1
	B	C
2	Elapsed time	time (in shifts)
3	11:30	1.44
Sheet1

Cell Formulas
Range	Formula
C3	C3	=B3*24/8

 

[smllchng5]

I tried that and it didn't quite work. This is the formula I used to get the turn around time. =(NETWORKDAYS(R7,S7,U$2:U$14)-1)*("16:30"-"07:30")+IF(NETWORKDAYS(R7,S7,U$2:U$14),MEDIAN(MOD(S7,1),"16:30","07:30"),"16:30")-MEDIAN(NETWORKDAYS(R7,S7,U$2:U$14)*MOD(S7,1),"16:30","07:30")
Then if I do the *24/9, I get 34:30:00. (If I don't do that. I get 11:30...I just need to format the cell to have a final outcome of 1.44. THe answer is correct in that it took 11 hours and 30 minutes, but I need to format the cells, I believe. Thanks for your help. I really appreciate it.

 

[Asbestos_Jen]

Can you upload the relevant cells for the formula you listed above using XL2BB, or at least provide a table with the values in cells R7, S7, and U2:U14? Without those values, I can't see how you get 11:30. I'm also not sure why you used an 8 hour day in post#1 and 24/9 in post#3.

 

[smllchng5]

Sadly I can't send the sheet as it has protected info on there..but here is the cells, so you can see. Hope this makes more sense. Thank you again. I really appreciate the help on this. ..(oh and it was a typo.. I meant *24/8. Sorry about that )

 

[Asbestos_Jen]

I don't get the same results as you using the data above. What is the format currently being used in cells (I think) P2:P8?
You don't need to share the whole sheet, just the relevant cells. The best way is with the XL2BB tool, like I have below, but if you can't get it to work, then a copy/paste of the table will go a long way so people don't have to re-type your data (and potentially make mistakes).

Book1
	N	O	P	Q	R	S	T	U
1	TAT [H]:mm *24/8	TAT [H]:mm	TAT decimal		Delivered Date/time	Completed Date/time		Holidays
2	0.00	0:00	0.00		11-Sep-24 10:00	11-Sep-24 13:00		1-Jan-24
3	0.00	0:00	0.00		13-Sep-24 09:00	13-Sep-24 14:00		15-Jan-24
4	0.00	0:00	0.00		13-Sep-24 10:00	13-Sep-24 15:30		27-May-24
5	0.25	2:00	0.08		23-Sep-24 16:30	24-Sep-24 09:30		19-Jun-24
6	0.00	0:00	0.00		27-Sep-24 09:00	27-Sep-24 11:15		4-Jul-24
7	1.44	11:30	0.48		08-Nov-24 15:00	13-Nov-24 10:00		2-Sep-24
8	1.34	10:45	0.45		22-Dec-24 15:30	27-Dec-24 09:15		11-Nov-24
9								28-Nov-24
10								29-Nov-24
11								24-Dec-24
12								25-Dec-24
13								31-Dec-24
Sheet1

Cell Formulas
Range	Formula
N2:N8	N2	=O2*24/8
O2:O8	O2	=(NETWORKDAYS(R2,S2,U$2:U$14)-1)*("16:30"-"07:30")+IF(NETWORKDAYS(R2,S2,U$2:U$14),MEDIAN(MOD(S2,1),"16:30","07:30"),"16:30")-MEDIAN(NETWORKDAYS(R2,S2,U$2:U$14)*MOD(S2,1),"16:30","07:30")
P2:P8	P2	=(NETWORKDAYS(R2,S2,U$2:U$14)-1)*("16:30"-"07:30")+IF(NETWORKDAYS(R2,S2,U$2:U$14),MEDIAN(MOD(S2,1),"16:30","07:30"),"16:30")-MEDIAN(NETWORKDAYS(R2,S2,U$2:U$14)*MOD(S2,1),"16:30","07:30")

 

[smllchng5]

Sadly it won't upload using XL2BB, (protected file) . It is okay. The formula your wrote does not calculate correctly on rows 2-6...so that is not going to work. Is there a way to get the 11:30 to show (11 hours 30 minutes) to shows as 11.50 (11 hours and .5 of an hour) I think it is just in the formatting of the cell, but can't figure out the correct format. [h]:mm is giving the 11:30. Thanks again for everything

 

[Asbestos_Jen]

I copied the formula you had in post #3 into columns O & P and filled it through the other cells. Column N is calculated from column O as shown. I need more information to help you.
1. What formula are you using in column P? Is it the same for all cells (except row references)?
2. What format are you using in column P?

 

[smllchng5]

So I just subracted S from R and it is just number format in cells on the ones that are within the same day. That is the issue with the column p is that I think the format is not correct. Regular numbers does not give me the number I need. Thanks again for your help.

 

[Asbestos_Jen]

I modified the formula in P (and O) to subtract S from R if they are the same day.
The result in P is formatted as decimal. O is the same formula as P, formatted as [h]:mm.
N is the result in P*24/8 to give number of shifts as a decimal.
M is the result in P*24 to give the number of working hours as a decimal.

2204-10-21.xlsx
	M	N	O	P	Q	R	S	T	U
1	TAT decimal *24 (as decimal)	TAT decimal*24/8	TAT [H]:mm	TAT decimal		Delivered Date/time	Completed Date/time		Holidays
2	3.000	0.375	3:00	0.125		11-Sep-24 10:00	11-Sep-24 13:00		01-Jan-24
3	5.000	0.625	5:00	0.208		13-Sep-24 09:00	13-Sep-24 14:00		15-Jan-24
4	5.500	0.688	5:30	0.229		13-Sep-24 10:00	13-Sep-24 15:30		27-May-24
5	2.000	0.250	2:00	0.083		23-Sep-24 16:30	24-Sep-24 09:30		19-Jun-24
6	2.250	0.281	2:15	0.094		27-Sep-24 09:00	27-Sep-24 11:15		04-Jul-24
7	11.500	1.437	11:30	0.479		08-Nov-24 15:00	13-Nov-24 10:00		02-Sep-24
8	10.750	1.344	10:45	0.448		22-Dec-24 15:30	27-Dec-24 09:15		11-Nov-24
9									28-Nov-24
10									29-Nov-24
11									24-Dec-24
12									25-Dec-24
13									31-Dec-24
Sheet1

Cell Formulas
Range	Formula
M2:M8	M2	=P2*24
N2:N8	N2	=P2*24/8
O2:O8	O2	=IF(INT(S2)=INT(R2),S2-R2,(NETWORKDAYS(R2,S2,U$2:U$14)-1)*("16:30"-"07:30")+IF(NETWORKDAYS(R2,S2,U$2:U$14),MEDIAN(MOD(S2,1),"16:30","07:30"),"16:30")-MEDIAN(NETWORKDAYS(R2,S2,U$2:U$14)*MOD(S2,1),"16:30","07:30"))
P2:P8	P2	=IF(INT(S2)=INT(R2),S2-R2,(NETWORKDAYS(R2,S2,U$2:U$14)-1)*("16:30"-"07:30")+IF(NETWORKDAYS(R2,S2,U$2:U$14),MEDIAN(MOD(S2,1),"16:30","07:30"),"16:30")-MEDIAN(NETWORKDAYS(R2,S2,U$2:U$14)*MOD(S2,1),"16:30","07:30"))