- Dec 26, 2024
Excel Custom Function for Common Dates
- Neale Blackwood
- Custom Functions, Dates
- 0 comments
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.
Sign up to hear about free Excel training.
I won't share your email with anyone.
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.
