Hi! Forgive this newbie's first post...
I need help in calculating the turnaround time for emails received and responded to.
Here are the conditions:
Cell A1: Received date & time / start date & time
Cell B1: Completion date & time / end date & time
Work hours: 9pm to 6am (this is my actual shift)
Work days: regular business days, does not include weekends and holidays
Items received and completed within the shift should count as zero "0"
Items completed on the next shift should count as one "1"
Items completed on the next next shift should count as two "2", etc etc...
So far, I have this formula: =NETWORKDAYS(A1,B1,Holidays)-IF(NETWORKDAYS(A1,B1,Holidays)>0,1,IF(WEEKDAY(A1,2)<6,-1,0))
While it works fine for anything I complete before 12mn, the items I complete after 12mn get tagged as 1 even if it is still within my shift. Likewise, anything I complete the next shift gets tagged as 2 even if in my count, it was only 1 day and so on. Is there a way for me to tweak the formula to show the turnaround time I need?
Thanks!
Bookmarks