I need help with creating a custom time format

smllchng5

New Member
Joined
Sep 24, 2024
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
BC
2Elapsed timetime (in shifts)
311:301.44
Sheet1
Cell Formulas
RangeFormula
C3C3=B3*24/8
 
Upvote 0
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. :)
 
Upvote 0
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.
 
Upvote 0
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 :( )

1729309132839.png
 
Upvote 0
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
NOPQRSTU
1TAT [H]:mm *24/8TAT [H]:mmTAT decimalDelivered Date/timeCompleted Date/timeHolidays
20.000:000.0011-Sep-24 10:0011-Sep-24 13:001-Jan-24
30.000:000.0013-Sep-24 09:0013-Sep-24 14:0015-Jan-24
40.000:000.0013-Sep-24 10:0013-Sep-24 15:3027-May-24
50.252:000.0823-Sep-24 16:3024-Sep-24 09:3019-Jun-24
60.000:000.0027-Sep-24 09:0027-Sep-24 11:154-Jul-24
71.4411:300.4808-Nov-24 15:0013-Nov-24 10:002-Sep-24
81.3410:450.4522-Dec-24 15:3027-Dec-24 09:1511-Nov-24
928-Nov-24
1029-Nov-24
1124-Dec-24
1225-Dec-24
1331-Dec-24
Sheet1
Cell Formulas
RangeFormula
N2:N8N2=O2*24/8
O2:O8O2=(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:P8P2=(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")
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
MNOPQRSTU
1TAT decimal *24 (as decimal)TAT decimal*24/8TAT [H]:mmTAT decimalDelivered Date/timeCompleted Date/timeHolidays
23.0000.3753:000.12511-Sep-24 10:0011-Sep-24 13:0001-Jan-24
35.0000.6255:000.20813-Sep-24 09:0013-Sep-24 14:0015-Jan-24
45.5000.6885:300.22913-Sep-24 10:0013-Sep-24 15:3027-May-24
52.0000.2502:000.08323-Sep-24 16:3024-Sep-24 09:3019-Jun-24
62.2500.2812:150.09427-Sep-24 09:0027-Sep-24 11:1504-Jul-24
711.5001.43711:300.47908-Nov-24 15:0013-Nov-24 10:0002-Sep-24
810.7501.34410:450.44822-Dec-24 15:3027-Dec-24 09:1511-Nov-24
928-Nov-24
1029-Nov-24
1124-Dec-24
1225-Dec-24
1331-Dec-24
Sheet1
Cell Formulas
RangeFormula
M2:M8M2=P2*24
N2:N8N2=P2*24/8
O2:O8O2=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:P8P2=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"))
 
Upvote 0
Solution

Forum statistics

Threads
1,224,567
Messages
6,179,564
Members
452,926
Latest member
rows and columns

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top