# Excel 2007 : Excel formula for calculating TAT minus weekends

1. ## 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. ## 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. ## 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. ## 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. ## 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

6. ## Re: Excel formula for calculating TAT minus weekends

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

7. ## 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. ## Re: Excel formula for calculating TAT minus weekends

NM, I understand it now. Thanks again.

Lop

9. ## 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. ## 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. ## Re: Excel 2007 : Excel formula for calculating TAT minus weekends

Try this version

Formula:
`Please Login or Register  to view this content.`

12. ## 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. ## 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.

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

#### 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