Finding the 15th of Next Month but Not on Weekends

[tsroque]

I need a formula to find the 15th of the NEXT month BUT if the 15th falls on a weekend, I need it to default to Monday. Any suggestions?

 

[NeonRedSharpie]

=IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,15),2)<6,DATE(YEAR(NOW()),MONTH(NOW())+1,15),IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,15),2)=6,DATE(YEAR(NOW()),MONTH(NOW())+1,15)+2,IF(WEEKDAY(DATE(YEAR(NOW()),MONTH(NOW())+1,15),2)=7,DATE(YEAR(NOW()),MONTH(NOW())+1,15)+1)))

It's a tad bit long and messy. But it should do what you want. Basically what this says is:

If the next month's 15 is equal to Monday through Friday (1-5) then return the 15th. Otherwise, if it's equal to Saturday (6) return the 15th + 2. Or, if it's Sunday (7), return the 15th plus 1.

 

[tsroque]

Ah! Perfect!
I actually changed the NOW() to reference a cell with a date, and it's right on the money!!!
Thank you so much!!!

 

[NeonRedSharpie]

I figured there might be a cell reference instead of the current date. Glad it worked out.

 

[Rick Rothstein]

Ah! Perfect!
I actually changed the NOW() to reference a cell with a date, and it's right on the money!!!
Thank you so much!!!

Assuming that cell reference is A1, then I think this formula will work correctly for you (test it, of course)...

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,14),1)

NOTE: If the answer to rollis13's question (Message #6) is "yes", then note that the WORKDAY function has an optional 3rd argument that lets you specify a range of cells containing holidays that will be skipped over).

 

[rollis13]

May I ask ? What if the 15th or the next monday is a national Holiday ? Would it care for you ?

 

[tsroque]

The formula isn't being used across the board but to automate our promotions. Holidays are calculated automatically since those are when our promos are displayed (ex. Black Friday). Basically we change our pricing every month on the 15th unless it falls on a Monday. Then the next level is just +1 day from that or End of Month (EOMONTH) UNLESS it follows a promotion.

I just tried it out for future years and it's PERFECT!

 

[rollis13]

Thank you for the feedback.