+ Reply to Thread
Results 1 to 13 of 13

Excel 2007 : Excel formula for calculating TAT minus weekends

  1. #1
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Excel formula for calculating TAT minus weekends

    I need a formula to add 42 hours (1.75 days) to a cell that contains a date and time minus weekends. I'm currnetly using =A2+B2+2*(WEEKDAY(A2)=6)but when I enter 6/16/11 7:07 AM in A2 and want to add 1.75 in B2 business days it shows 6/18/2011 1:07 AM but I need it to show 6/20/2011 1:07 AM based on a 24 hour clock

  2. #2
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel formula for calculating TAT minus weekends

    The formula seems to work for all start days of the week except Thurdays, not sure why, any help would be wonderful.

  3. #3
    Registered User
    Join Date
    06-15-2011
    Location
    S. Carolina
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Excel formula for calculating TAT minus weekends

    Would this work for you? Just guessing.

    Something like =A2+B2+(WEEKDAY(A2)=6)+2

    Loppy

  4. #4
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel formula for calculating TAT minus weekends

    WOW, Loppy, that seemed to fix the issue I was having, TY so much for the quick reply, I had no idea it was going to be that easy. Thank you.

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Excel formula for calculating TAT minus weekends

    I don't think that will work in all circumstances though - what if A2 is Friday at 07:00 and B2 is 1.75 - that formula will give you next Wednesday at 01:00 rather than next Tuesday.

    You could use WORKDAY like this

    =WORKDAY(A2,B2+MOD(A2,1))+MOD(A2+B2,1)

    where A2 is any weekday date/time
    Audere est facere

  6. #6
    Registered User
    Join Date
    06-15-2011
    Location
    S. Carolina
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Excel formula for calculating TAT minus weekends

    Actually, now looking at it further, Does your Friday come out correct now?

  7. #7
    Registered User
    Join Date
    06-15-2011
    Location
    S. Carolina
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Excel formula for calculating TAT minus weekends

    There ya go, Daddylonglegs got it. Very cool. Yeah, when I looked at mine after the fact, Friday's didn't work. heh.

  8. #8
    Registered User
    Join Date
    06-15-2011
    Location
    S. Carolina
    MS-Off Ver
    Excel 2016
    Posts
    15

    Re: Excel formula for calculating TAT minus weekends

    NM, I understand it now. Thanks again.

    Lop
    Last edited by Loppy; 06-16-2011 at 03:49 PM.

  9. #9
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel formula for calculating TAT minus weekends

    Thank you Daddylonglegs, your formula seems to work correctly for any day, Thank you so much for the help.Good day to everone.

  10. #10
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel formula for calculating TAT minus weekends

    Hello, I'm now working in excel 2010 and have added a holiday list to my calculations but I seem to be having an issue when the start date is a weekend day or holiday. The formula reads: =WORKDAY(A2,B2+MOD(A2,1), HolidayList!A$2:A$100)+MOD(A2+B2,1). example: B2=1.75 and I have 1/21/2013 in the holiday list. The formula works fine as long as the start date is not a weekend or holiday. A2=1/18/2013 2:43:16 PM the correct TAT is shown as 2013/01/23 8:43 AM. However if I enter the weekend or holiday in A2 the formula still returns the same value, when it needs to return a the value 1/23/2013 6:43:16 PM instead. (Anything that comes in on the weekend and holiday needs to have the start tiem adjusted to midnight of the next business day). The formula I use to calcualte the TAT is =(NETWORKDAYS(A2,B2,HolidayList!A$17:A$407)-1)*(J$3-J$2)+IF(NETWORKDAYS(B2,B2,HolidayList!A$17:A$407),MEDIAN(MOD(B2,1),J$3,J$2),J$3)-MEDIAN(NETWORKDAYS(A2,A2,HolidayList!A$17:A$407)*MOD(A2,1),J$3,J$2)

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Excel 2007 : Excel formula for calculating TAT minus weekends

    Try this version

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 : Excel formula for calculating TAT minus weekends

    Thank you for the fast reply I tried your versin above and it still appears to have an error. If use the start date of 2013/01/18 2:43 PM the formula you provided returns a result of 2013/01/22 8:43 AM. This appears to be 24 hours off because when I put the start and end date stamps in the TAT formula it returns a valve of .75 and not 1.75 days. If I use a start time that falls on weekend or holiday your formula works correctly.

  13. #13
    Registered User
    Join Date
    06-16-2011
    Location
    sacramento, CA
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Excel 2007 : Excel formula for calculating TAT minus weekends

    Thank you for the fast reply I tried your versin above and it still appears to have an error. If use the start date of 2013/01/18 2:43 PM the formula you provided returns a result of 2013/01/22 8:43 AM. This appears to be 24 hours off because when I put the start and end date stamps in the TAT formula it returns a valve of .75 and not 1.75 days. If I use a start time that falls on weekend or holiday your formula works correctly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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