2

Looking for some assistance on why my calculated column formula doesn't work when there is 5 weeks in the month.

I have one column called Current Patching Date (date/time field) - stores a date

I have one column called Next month calculated (calculated column) - this calculates the month following the date in the Current Patching Date column

I have one column called 3rd Tue-Next Month Patch Date (calculated column) - this should calculate the 3rd Tuesday in the month. I use the following formula for this column: 

=DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),(3-MOD(WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)),7)+15))

This formula works fine if there is only 4 weeks in month, but doesn't work if there are 5 weeks in the month. Does anyone have any insight into why this would be? This is the only formula I have found searching the web that is remotely close to what I need to accomplish.

Improve this question

1 Answer 1

Reset to default
2

I mocked this up and am seeing that months starting with Sunday, Monday, or Tuesday (1, 2, 3 in your WEEKDAY formula) are returning the 3rd Tuesday of the month, while months starting with Wednesday-Saturday (4-7 values) are returning the 2nd. This doesn't appear to be tied to the number of Tuesdays in a month, but rather a negative value from this portion of your calculated column:

3-MOD(WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)),7)

Take a month starting with Monday (2) for example. 3-2 = 1, +15 is 16. This works. But then take one starting with Friday (6). 3-6 = -3, +15 is 12 - when what you want it to say is 19. This is where your error is coming from.

So what we have to do is account for those months where your starting date is a negative. We can solve for this with an IF block. Try this - it's working in my lab:

=IF((WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)) < 4), DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),(18-WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)))), IF((WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)) < 7), DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),(3-MOD(WEEKDAY(DATE(YEAR([Next month calculated]),MONTH([Next month calculated]),1)),7)+22)), DATE(Year([Next month calculated]), MONTH([Next month calculated]), 18)))

It's clunky, but it should get the job done for you.

Improve this answer
2
  • 1
    That worked perfectly. Thank you so much. That makes alot of sense. I applied this formula to another column in which I needed to get the same thing but for the 3rd Wednesday of the month and just tweeked the numbers and it worked.
    – heraldch
    Jul 6, 2017 at 12:25
  • Happy to hear it! Glad I was able to help - and even learned something new myself in the process :)
    – Brendan W
    Jul 6, 2017 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.