Formula to get the 4th workday before end of month

[vinzent]

Hello,
I want to get one formula to get the 4th workday before end of month (not weekends)
I was trying with this formula, but is not working correctly (is showing 4/26 instead 4/25)

=DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 0) - 4

Example:

Month	4th workday before end of month
Apr	25-Apr
May	28-May
Jun	25-Jun
Jul	26-Jul
Aug	27-Aug
Sep	24-Sep
Oct	28-Oct
Nov	26-Nov
Dec	26-Dec

Thank You for your help.

 

[Tetra201]

See if the following formula works for you:

=WORKDAY(EOMONTH(TODAY(),0)+1,-4)

 

[vinzent]

See if the following formula works for you:

=WORKDAY(EOMONTH(TODAY(),0)+1,-4)

Hello
Thank You for your help, but this works for this month, how to get the same information for next months?

 

[Tetra201]

The following formula will spill the required dates for a year ahead:

=WORKDAY(EOMONTH(TODAY(),{0;1;2;3;4;5;6;7;8;9;10;11})+1,-4)

 

[MARK858]

@vinzent is the 30th September a holiday where you are?

 

[kvsrinivasamurthy]

Try for year 2024

=WORKDAY(EOMONTH(DATE(2024,{1,2,3,4,5,6,7,8,9,10,11,12},1),0),-4)

For 6th month

=WORKDAY(EOMONTH(DATE(2024,6,1),0),-4)

 

[vinzent]

@vinzent is the 30th September a holiday where you are?

I need to skip this date, so the date will be 9/24.

 

[MARK858]

Then you will need to create a list of holiday dates in a range so it can be added to the proposed formulas that have been posted.
Only on my phone at the moment and so will update one of the formulas when I get in later if no-one else has done but we need to know the range with the holidays.

 

[Tetra201]

I need to skip this date, so the date will be 9/24.

In this case, put all your dates-to-skip in a range (say, X1:X100) and use the following updated formula:

=WORKDAY(EOMONTH(TODAY(),{0;1;2;3;4;5;6;7;8;9;10;11})+1,-4,X1:X100)