Counting days back from a date

**blkirby** · 08-17-2012, 04:46 PM

Hello,

My name is Becky and I'm new to the forum.

I have a spreadsheet that takes the date in a particular cell and counts back a certain number of calendar days. If the date falls on a weekend or holiday, it goes back to the next earlier business day.

Here is the formula:

=WORKDAY(C3-29,-1,Holidays)

The point is to take the date in C3 and count back 30 calendar days. So I subtract 29 days from C3 up front and then take a way one day. "Holidays" refers to a section elsewhere in the spreadsheet that lists a number of state holidays. So, if the date falls on Saturday or Sunday, it formula will give me Friday.

This works great. However, sometimes I need to actually go forward to the next business day instead of back. So, if the date calculated with C3-29, -1 falls on a Saturday or Sunday, I need it to give me the following Monday. How can I accomplish this?

Thanks.

**Ace_XL** · 08-17-2012, 04:56 PM

=WORKDAY(C3-29,1,Holidays) will take you a day forward

**blkirby** · 08-17-2012, 05:14 PM

Thanks for your reply, but that won't work for me. Let me try to explain explain

For example, with the formula as I currently have it [=WORKDAY(C3-29,-1,Holidays)], If C3 = 7/22/2013, then I will be given the date 7/21/2013 because C3-29,-1 comes to a Saturday, 7/22. The next earlier business day is Friday 7/21.

If I change the formula as you suggest to =WORKDAY(C3-29,1,Holidays), and C3 = 7/22/2013, I will get the date I am looking for, Monday, 7/24 because C3-29 falls on a Sunday and adding 1 gives me Monday. But this won't work in all instances. For example if C3 = 7/19/2013, I will get Friday, 7/21. What I need to get is Wednesday, 7/19, because that is the date that is 30 days before 7/19.

So I need a formula that will work in all instances. I need it to take a date, subtract 30 days, and if the resulting date falls on a business day (Monday through Friday that is not a state holiday), great, give me that date. But, if the date falls on Saturday, Sunday, or a state holiday, I need the next business day closer to the original date.

Hope that makes sense.

**Ace_XL** · 08-17-2012, 11:09 PM

In that case, how about..

=IF(OR(WEEKDAY(C3-30)={1,7}),WORKDAY(C3-30,1,Holidays),WORKDAY(C3-30,0,Holidays))

**daddylonglegs** · 08-19-2012, 12:40 PM

Generically if you want to go back to the previous workday you would use

=WORKDAY(x+1,-1)

[where x is the date returned by C3-30]

and if you want to go forward that will be

=WORKDAY(x-1,1)

so you need

=WORKDAY(C3-31,1,holidays)

**blkirby** · 08-20-2012, 03:02 PM

Originally Posted by daddylonglegs

so you need

=WORKDAY(C3-31,1,holidays)

Thank you daddylonglegs. That worked perfectly. And thanks to Ace_XL for replying to my post.

-Becky

**Cutter** · 08-20-2012, 07:24 PM

@ blkirby

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.