Adding dates ... to previous weekday if falls on weekend.

[StargateFanWrk]

I found lots of solutions if one is using the AnalysisPak but I'm not and
with no possibility to use it since they don't like us installing stuff,
even things like this.

I have this formula:
=IF(A2<>"",A2+18,"")

It works fine except it deals with calendar days. The formula putting
today's date in A2, returns this date:
Sat 2009.12.19

This is almost good enough but I need it to go to the nearest weekday, which
would be Friday, Dec.18.2009.
And if the date came up as, say, Sunday December 20th, same thing. The
value returned for either weekend day would be the previous Friday.

How can one do this, pls?

Thanks. D

 

[Bernard Liengme]

Try
=A1+18+(WEEKDAY(A1+18,2)=6)*2+(WEEKDAY(A1+18,2)=7)

This add 2 if the result (A1+18) is Saturday or 1 if the result is Sunday
best wishes

 

[StargateFanWrk]

Try this...

A2 = some date
B2 = number of days (can be either a positive or negative number)

=IF(A2="","",A2+B2+LOOKUP(WEEKDAY(A2+B2),{1;2;7},{1;0;-1}))

Or...

=IF(A2="","",A2+B2+CHOOSE(WEEKDAY(A2+B2),1,,,,,,-1))

Hello, thank you! I don't seem to have the hang of this one yet. Perhaps I
should have explained what I was trying to do rather than the usual way of
trying to modify a formula <g>.

I have a frequent task to do which is to send requests to a printer (i.e.,
business cards, etc.). They've advised me here that the due date for
delivery of these items from the printer is 15 business days (18 calendar
days) from the date the request comes in. It seemed easier to just count
out 18 days from the date I entered into A2 and then pedal back to nearest
Friday preceding any date that falls on the weekend. I know this can be
done, I just don't know how to do it. When I tested the above formula, it
didn't work because in one case I did get a Monday date which is beyond the
due date even though technically it would probably be alright it would be
the nearest workday, too, but it's _after_ the limit, not _before_ and it
doesn't feel right. Better to err on the other side and say that due dates
are before, not after, weekend days I think.

Now it could be that I didn't modify the formula correctly. Here's what I
changed it to:
=IF(A2="","",A2+18+LOOKUP(WEEKDAY(A2+18),{1;2;7},{1;0;-1}))
(replacing B2 reference with the number 18 representing the calendar days)

The same thing happens with the second formula, too since I got a Monday
result during testing.

You folks that are so good at these things, perhaps adding 18 calendar days
and adjusting to a weekday may not be best approach? I can't think of
another way, though. What does anyone suggest?

Thanks! D

 

[Ron Rosenfeld]

I found lots of solutions if one is using the AnalysisPak but I'm not and
with no possibility to use it since they don't like us installing stuff,
even things like this.

I have this formula:
=IF(A2<>"",A2+18,"")

It works fine except it deals with calendar days. The formula putting
today's date in A2, returns this date:
Sat 2009.12.19

This is almost good enough but I need it to go to the nearest weekday, which
would be Friday, Dec.18.2009.
And if the date came up as, say, Sunday December 20th, same thing. The
value returned for either weekend day would be the previous Friday.

How can one do this, pls?

Thanks. D

=IF(A2="","",A2+18-(WEEKDAY(A2+18,2)>5)*(WEEKDAY(A2+18,2)-5))

Or, more generally:

A1: Starting Date
A2: Number of days to add

=IF(A1="","",A1+A2-(WEEKDAY(A1+A2,2)>5)*(WEEKDAY(A1+A2,2)-5))

--ron

 

[StargateFanWrk]

=IF(A2="","",A2+18-(WEEKDAY(A2+18,2)>5)*(WEEKDAY(A2+18,2)-5))

Or, more generally:

A1: Starting Date
A2: Number of days to add

=IF(A1="","",A1+A2-(WEEKDAY(A1+A2,2)>5)*(WEEKDAY(A1+A2,2)-5))

--ron

Okay, I think I might have goofed earlier in my counting. I was counting
the day itself because the early formulas didn't seem to work. I should
have resumed starting counting after the start date once I was working with
the corrected formulas. My bad, as the saying goes. The +18 formula seems
to work above just fine. I'll have to go back and try the other ones again,
too, to make sure it wasn't just my bad counting at fault there, too!
<sigh>

Thanks so much. You're all life-savers. So much happens in this position
that I want to make sure that the tracking system we all use reflects
correct information on my part. A calculation of this kind ensures I put
the right due dates in at all times! D

 

[T. Valko]

My fault!

I thought I had read that:

If date + 18 = Saturday then roll back to Friday
If date + 18 = Sunday then roll forward to Monday

These will roll back to Friday for either case:

=IF(A2="","",A2+18+LOOKUP(WEEKDAY(A2+18),{1;2;7},{-2;0;-1}))

=IF(A2="","",A2+18+CHOOSE(WEEKDAY(A2+18),-2,,,,,,-1))

 

[StargateFanWrk]

My fault!

I thought I had read that:

If date + 18 = Saturday then roll back to Friday
If date + 18 = Sunday then roll forward to Monday

These will roll back to Friday for either case:

=IF(A2="","",A2+18+LOOKUP(WEEKDAY(A2+18),{1;2;7},{-2;0;-1}))

=IF(A2="","",A2+18+CHOOSE(WEEKDAY(A2+18),-2,,,,,,-1))

Thank you!!! Will give this a try, too. D

 

[Nate Hulee]

Hi Biff

The formula
=IF(A2="","",A2+18+LOOKUP(WEEKDAY(A2+18),{1;2;7},{-2;0;-1}))

The above formula works well for my needs but is missing the ablity to take into account public holidays, how would i add public holidays to the above formula so that if the date is a public holiday or a weekend it rolls back to the first workday

Thanks