Forum Discussion
Solved
Trying to calculate Turn around time based on formula found here but error
Dear All,
I am trying to calculate Turn around time based on working hours and holidays but for some reason I keep getting some numbers which are not ok.
For example. first highlighted row,I do not understand why the calculation resulted in ###
2nd row is fine, and it is what I would expect to happen with the calculation if the received or replied is outside working hours.
3rd row doesn't count the days, received is Jan and Relied is February
And lastly, some received are in 2023 and replied in 2024, this is again not getting calculated.
The formula used is the one in column F,
Could someone pretty please can help me with this? My mind is spinning, I am not an Excel expert but been trying to find a solution for days.
Thanks so so much. Any help is greatly appreciated.
rachel you are absolutely right! I tried and it works perfectly! Thank you so much for the help provided. You have helped me tremendously!
Hi,
Are you trying to use below formula?
https://exceljet.net/formulas/get-work-hours-between-dates-and-times
If so, I think you can first use the formula to calculate working hours between 9:00 and 18:00. and then use that formula to calculate working hours between 13:00 and 14:00. and the difference of the two should be want you want:
rachel thanks so much. It does seem to work if I add in data to the file you provided. However, I am getting the data from outlook via query, and added a column at the end. I calculate the hours between 9:00 to 18:00 row 22 gives 7:11 instead of 151:11 as in the file you provided. Any idea why this could be? thanks so much
Hi, I think the problem is formatting.
Instead of h:mm, you need to use [h]:mm
The latter will display hours > 24.
