Turn Around Time Calculation

[prasaddn]

Hi All,

I am trying to find the time taken to respond to the email received. so, I have time the email was received and the time email was responded.

The catch or conditions are as follows:
1. Working hours - 8 am to 5 pm.
2. Email received after 3 pm, automatically considered as received next day.
3. Saturdays and Sundays are not working days, and have holidays list as well.

Attached is the file that gives a clear picture.

I am able to get the solution using formulas in piece by piece and then summing up (refer cell H8). I need formula in one single cell (H12 or H13).

Some where my formula is short of 1 hr!!

Interesting if the received time is post 3 pm, my current formula in H12 or H13 works fine, but when you try to put the time less than 3 pm, it fails by 1 hour.

 

[prasaddn]

Feel free to ask questions, if any.

looking to get some solution.

Regards,
Prasad DN.

 

[prasaddn]

Hi,
I forgot to add some more clarity on point 2, if the email is received today after 3 pm, then the start time would start as tomorrow 8 am.

And, on same criteria, if email is received today at 4 am, the start will apply only from today 8 am. This is also not working in my so assumed working Perfect cell (H8).

Looking for some insights and help.

Regards,
Prasad DN

 

[PaulF]

To start instead of =TIME(HOUR(D8),MINUTE(D8),SECOND(D8)) to pull time only from a full date & time you can: =MOD(D8,1) << Then set cell format to the Time format you want.

Understanding that Excel store dates and times in a number format. Date to the left of the decimal and times to the right... So you can pull date w/ =INT(CELL) and time w/ = MOD(CELL,1)

I'm looking at the rest now

 

[snjpverma]

@Prasad, Why is your cell E13 reflecting as 8 instead of 9 ? i believe 8 am to 5 pm should be 9 hours. any reason for the same ?
I made a small change to your formula in H13 and it's working fine. The changes have been highlighted in red below

=(((NETWORKDAYS(D8,F8,J8:J16)-1)-(HOUR(D8)>=15))*0.375)+IF(HOUR(D8)>=15,G9-D4,G9-E9)

OR

try the below formula without the helper cells (E9 and G9)
=(((NETWORKDAYS(D8,F8,J8:J16)-1)-(HOUR(D8)>=15))*0.375)+IF(HOUR(D8)>=15,MOD(F8,1)-8,MOD(F8,1)-MOD(D8,1))

 

[prasaddn]

Thank you @PualF, @snjpverma.. i have incorporated both the suggestions, i.e using int() and mod() functions instead of date() and time() functions. And, also the new formula without helper column to calculate the TAT in H14 cell.

When I give the received time before 3 pm, it is working fine, it is not working on the following scenarios:

1. if the received time is after 3 pm, it shows ######### as value of TAT. attached is the updated file.

I need help on following as well:

2. like I mentioned in post #3 if the received time is before 8 am, it shows incorrect TAT. example i give received time is 7.30 am and responded time is 8.30 am, it should show 30 mins, but is showing 1 hr.
3. I may not have mentioned earlier, if any time responded is after 5 pm, until next day 8 am it is considered only till 5 pm. I am not sure if I am explaining this clearly. It is just to say if I work over time (after office hours), i get the benefit of it. Example. if the received time is 2.30 pm, and if I responded at 5.30 pm or 7.59 am next, the TAT should 2.5 hrs only.

Need help on this.

Regards,
Prasad DN