# 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  Register To Reply

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.  Register To Reply

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  Register To Reply

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.  Register To Reply

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  Register To Reply

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

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

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.  Register To Reply

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

NM, I understand it now. Thanks again.

Lop  Register To Reply

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.  Register To Reply

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)  Register To Reply

11. ## Re: Excel 2007 : Excel formula for calculating TAT minus weekends

Try this version

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

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.  Register To Reply

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.  Register To Reply