I need an excel formula to not update a cell if the date is past the last day of that month

[tewell53]

I am trying to create a formula that populates the Wodkdate as column headings for each month, each month is a separate worksheet. The formula I am using now is

=WORKDAY(D$4,1,Holidays)

Each cell reads the previous cell "D$4" increments by 1 and skips my list of holidays, based on the results from the above formula I have the following formulas"

The day

=DAY(J4)

The Day name

=TEXT(J4,"DDD")

I want to have a formula that when it reaches the last working day for a month, the first formula populates spaces and not the first working day of the following month.

For example, our last working day in November 2025 is November 26

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’).

The newer version of Excel has some cool functions that can eliminate redundancy in formulas and make stuff like this a little easier.

 

[Joe4]

Here is a version that should work on all versions of Excel.

Assuming the D4 is the first cell you type your initial date in, place this formula in E4 and copy across for all columns:

=IFERROR(IF(MONTH(WORKDAY(D$4,1))=MONTH(WORKDAY($D$4,1)),WORKDAY(D$4,1),""),"")

And here is the formula for D5, which you can copy across for all cells:

=IF(D4<>"",DAY(D4),"")

The formula for row 6 should be fine as-is.

 

[tewell53]

Here is a version that should work on all versions of Excel.

Assuming the D4 is the first cell you type your initial date in, place this formula in E4 and copy across for all columns:

=IFERROR(IF(MONTH(WORKDAY(D$4,1))=MONTH(WORKDAY($D$4,1)),WORKDAY(D$4,1),""),"")

And here is the formula for D5, which you can copy across for all cells:

=IF(D4<>"",DAY(D4),"")

The formula for row 6 should be fine as-is.

Joe4, Thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!

Obviously, you can add the Holidays argument back to the Workday functions, as needed.

 

[Joe4]

Since you are using a new version of Excel, you can make use of the LET function to shorten the first formula I gave you a little bit:

=LET(m,WORKDAY(D$4,1),IFERROR(IF(MONTH(m)=MONTH(WORKDAY($D$4,1)),m,""),""))