+ Reply to Thread
Results 1 to 3 of 3

Help computing turnaround time for night shift

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Help computing turnaround time for night shift

    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!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Help computing turnaround time for night shift

    If you work 9pm until 6am presumably the last shift of your week is from Friday at 9pm until Saturday at 6am and then the next shift starts at 9pm on Monday?

    Try just this:

    =NETWORKDAYS(A1-"6:00:01",B1-"6:00:01",Holidays)-1
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Help computing turnaround time for night shift

    Quote Originally Posted by daddylonglegs View Post
    If you work 9pm until 6am presumably the last shift of your week is from Friday at 9pm until Saturday at 6am and then the next shift starts at 9pm on Monday?

    Try just this:

    =NETWORKDAYS(A1-"6:00:01",B1-"6:00:01",Holidays)-1
    Thanks you very much, daddylonglegs! It works! ^_^
    Last edited by hypothetical; 04-01-2013 at 10:40 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1