determine the last business day before 15th of each month

Hi,

I need to determine the last business day before 15th of each month and last business day of each month. The equitation (Second_pay_day) returns the value and format that I want, but how to apply it to the business day before 15th? For example, Second_pay_day returns the exact last business day of each month if I change any_date to 11/1/2019 or 12/1/2019. What I want is using the same method to returns value 10/11/2019 for October, 10/13/2019 for November and 12/11/2019 for December......

Please Login or Register  to view this content.

Possibly...

Please Login or Register  to view this content.

Originally Posted by pmchris

I need to determine the last business day before 15th of each month and last business day of each month.

What I want is using the same method to returns value 10/11/2019 for October, 10/13/2019 for November and 12/11/2019 for December......

Your examples above do not seem to make sense to me. What exactly is a "business day" for you... the day before the 15th if it is a weekday or the Friday before the 15th?

Maybe I did not express clearly. so if the 15th is business day, then the last business day sits on 15th, if the 15th is weekend, then the last business day should be Friday before the 15th. for example, I want to return 10/15/2019 for October 2019, return 11/15/2019 for November 2019, and return 12/13/2019 for December 2019.

BTW, is there easy way to return what I want? for example, I use Second_pay_day = WorksheetFunction.workday(DateSerial(Year(any_date), Month(any_date) + 1, 1), -1) to get last business day of moth, is there similar expression?

Seems like there should be a shorter formula than this, but this is what I came up with. Here I have assumed your "any_date" is in cell A1.

Formula:

Please Login or Register  to view this content.