Friday, July 20, 2018

yogi_Find Date Of Every Last Specified Weekday (Tuesday) Of The Month Following The Date In Specified Cell (S3)

Google Spreadsheet   Post  #2477

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jul-20-2018

question by: Patrick Lathrop
https://productforums.google.com/forum/?utm_medium=email&utm_source=footer#!topic/docs/moqqtpK4UYE;context-place=forum/docs
Last (weekday) of following month formula for creating payment schedules?
So, with a little help from the help community i was able to put together an Nth day occurrence script that cleverly  determines the weekday associated with an original date (S3) and then counts the number of days in the current month to produce an accurate 1st 2nd 3rd or 4th (weekday) occurrence.

Unfortunately this clever bit of work, while accurate up to 2102 in my testing for 1st~4th occurrences gets broken by people who are assigned a last (weekday) of month schedule.

The current formula is as follows:

ARRAYFORMULA(S3+LOOKUP(COUNTIF(WEEKDAY(ROW(INDIRECT("M"&EOMONTH(S3,-1)+1&":M"&EOMONTH(S3,0)))),WEEKDAY(S3)),{4,5},{28,35}))
Is there a clever way to retrofit this formula to work with a LAST (weekday) payment schedule? Or an easier way to make a separate formula to detect the weekday and display the last weekday of the following month?

As example 7/31 (TUESDAY) and display 8/28/18 aka the last Tuesday of august 2018?

I'm including a copy of the spreadsheet i'm working on for reference, any and all help is greatly appreciated.

https://docs.google.com/spreadsheets/d/1MwKO0ZrRt9tw9yuFxyaVDI9PnvmicPj7Nq7PoWSjqhY/edit?usp=sharing