eomonth

[Stclements1]

I have this formula which I am grateful for the help from MrExcel users guidance, but having used it,the problem I have is that it can give me the start and end date of the same month as I asked for it to give me the last Workday at the end of the month but in reflection what I actually want it to do is give me the first Workday of each month. I have added a +before the final numeric 1 but that changed nothing.
=IF(ROWS(E$2:E2)>($B$4*12);"";WORKDAY(EOMONTH(B$2;D2-1)-1;1))

 

[Stclements1]

Sorry to waste your time, I have managed to correct it by making the changes as per so
=IF(ROWS(E$2:E17)>($B$4*12);"";WORKDAY(EOMONTH(B$2;D17-1)*1;1))

 

[Dave Patton]

The following may help

T202008a.xlsm
	B	C	D
2	5-Aug-20	31-Aug-20	Tue 1-Sep-20
3	5-Aug-20		Tue 1-Sep-20
4
2c

Cell Formulas
Range	Formula
B2	B2	=TODAY()
C2	C2	=EOMONTH(B2,0)
D2	D2	=WORKDAY(C2,1)
D3	D3	=WORKDAY(EOMONTH(B3,0),1)

 

[Stclements1]

Thank you for that I have now got the dates working on the first day of the month, myonly problem is that one of them is the 1st of January which is obviously an holiday but I can't work out how to eliminate holidays, I tried networkdays but that didn't work?

 

[Dave Patton]

try checking Excel's help for Workday

T202008a.xlsm
	B	C	D
4	15-Dec-20		Mon 4-Jan-21
5
2c

Cell Formulas
Range	Formula
D4	D4	=WORKDAY(EOMONTH(B4,0),1,Holidays)

Named Ranges
Name	Refers To	Cells
Holidays	='2c'!$F$1:$F$2	D4

 

[Stclements1]

I did look at this and have made a list of holiday dates that I can now add to the formula. I was under the impression that excel stored these automatically. Anyway many thanks for your help and guidance.

 

[Dave Patton]

Thanks for the feedback. Dave