Determing the last workday of the month

[UFGATORS]

Hello, I'm hoping someone can assist me, I need to have a formula that calculates when the last workday of the month is, holidays would not be counted as work days. I then need for "EOM" to appear in the column representing the last working day of the month. Currently I have this formula that places "EOM" over the last day of the month. =IF(EOMONTH($L$2,O)=AJ2,"EOM"), this formula is entered in cells AJ1 - AP1. Cell L2 contains the month and year. For the new formula I do have a named range for the holidays, so I need the formula to be able to determine which day of the month is the last workday and have it enter "EOM" over that day AJ1 - AP1 . Below is another formula that I think is part of the answer but I don't know how join the two together. =NETWORKDAYS(L2,EOMONTH(L2,0),HOLIDAYS).

Thanks for the help

 

[Beyond_avarice]

I can you help you find the last Weekday with the following:

=IF(WEEKDAY(EOMONTH(L2,0),1)=1,EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0)))

The Holiday list is throwing me. The following will at least recognize the last weekday to your restriction list and take away one day. This wont work if you have two restricted weekdays.

=IF(MATCH(IF(WEEKDAY(EOMONTH(L2,0),1)=1,EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0))),HOLIDAYS,0)>0,IF(WEEKDAY(EOMONTH(L2,0),1)=1,EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0)))[COLOR=#ff0000]-1[/COLOR],"")

Instead of subtracting 1, I suppose an index match could be used to indicate how many days to subtract to get to the first working weekday. This appears to work.

=IF(MATCH(IF(WEEKDAY(EOMONTH(L2,0),1)=1,EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0))),HOLIDAYS,0)>0,IF(WEEKDAY(EOMONTH(L2,0),1)=1,EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0)))+INDEX(HOLIDAYS,MATCH(IF(WEEKDAY(EOMONTH(L2,0),1)=1,-EOMONTH(L2,0)-2,IF(WEEKDAY(EOMONTH(L2,0),1)=7,EOMONTH(L2,0)-1,EOMONTH(L2,0))),HOLIDAYS,0),2),"")

My example is September 2017. The EOM is 9/30/17 which is a weekend. The last working day would be 9/29/17. I'm acting as though my HOLIDAYS indicates that the following are HOLIDAYS and if one of these is landed on, subtract to the last workday(index column=2):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]9/29/17
[/TD]
[TD]-3
[/TD]
[/TR]
[TR]
[TD]9/28/17
[/TD]
[TD]-2
[/TD]
[/TR]
[TR]
[TD]9/27/17
[/TD]
[TD]-1
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe this can help
Excel formula: Get last working day in month | Exceljet

M.

 

[UFGATORS]

Yes it did help, I got it to work. Thank you

 

[UFGATORS]

Thanks for your help, I was able to get it to work with the assistance of another user. I was close, here is the formula I used =IF(WORKDAY(EOMONTH($L$2,0)+1,-1,holidays)=AJ2,"EOM",""), this formula was entered above each day of the month starting on the 25th of the month to the end of the month. Thanks again.