Excel has an EOMONTH functions that returns the end of month. You can hack this function to return the start of month as well. I thought I would make it easy to return the first, last and middle of the month in one simple function.
Before creating the custom function, I did some testing with the SWITCH function – see image below.
The formula in cell B3 has been copied across to column D and copied down. It is.
=SWITCH(B$2,"s",EOMONTH($A3,-1)+1,"m",DATE(YEAR($A3),MONTH($A3),15),"e",EOMONTH($A3,0))This uses the SWITCH function to choose the correct formula as required by each of the three options: start (s), middle (m), or end (e).
LAMBDA version
To test the LAMBDA function, I used the following formula in cell F3. This has been copied across to column H and down.
=LAMBDA(dte,when,SWITCH(when,"s",EOMONTH(dte,-1)+1,"m",DATE(YEAR(dte),MONTH(dte),15),"e",EOMONTH(dte,0)))($A3,B$2)
The range name definition used is.
The custom function in use is shown below. Again the formula has been copied across and down.
For versions of Excel supporting dynamic arrays, here is a formula that will spill all three columns of results for a single given date… just put it in cell B3 and copy down.
=DATE(YEAR(A3),MONTH(A3),HSTACK(1,15,DAY(EOMONTH(A3,0))))
Here is a formula using the same method as above but which will process an entire range of dates without having to copy anything down (note that it also outputs the headers as well).
=REDUCE({“s”,”m”,”e”},A2:A7,LAMBDA(a,x,VSTACK(a,DATE(YEAR(x),MONTH(x),HSTACK(1,15,DAY(EOMONTH(x,0)))))))
Thanks for the suggestions Rick.