Hello, Excellers and welcome back to another #FormulaFriday #Excel blog post in my Excel series. Today I want to answer a question that a subscriber sent to me. If you want to ask any questions or comment feel free to leave a comment below. The question was, how to use the IF Function in Excel to identify if a cell contained the last day of the month.
So, why did they want to know this?. Well, on the last day of the month, Excel has to automatically insert 100 Euros into a budget column as their allowance. This is represented as by a very basic version of the Budget Tracker below in my screengrab.
You can see that on the last day of the month 100 Euros is set to automatically be inserted into the Payment In Allowance Column in my Excel worksheet.
Solution.
So, I suggested that we use the EOMONTH Function along with the IF Function. The logic behind this is to test the Date in Column C. If the date in Column B is the last day of the month, then 100 Euros is entered into the cell in Column C. If the date is not the end of the month then nothing is entered. Let’s write the formula!.
EOMONTH Function.
Let’s take the EOMONTH Function first and look at the syntax.
=EOMONTH(start_date, months) this function returns the serial number of the last day of the month before or after a specific number of months.
where
start_date – this is a serial date number that is your start date
months – this is the number of months before or after the start date
If we type this into our example in Column C, the start date is B4 and the month argument I have set at 0 which indicates which represents the current month of the start date in B4. The result returned is the last date of the month.
Now I have that part of the formula I can now build my logic into it with the If Function. Sounds good right?.
The IF Function.
Let’s take a quick recap of the IF Function which checks whether a condition is met. It will return one value if the condition is TRUE and another if it is false. The syntax is as follows:-
=IF (logical_test, [value_if_true], [value_if_false])
Where
logical_test – the value or logical expression to be evaluated as TRUE or FALSE
value_if_true – the value returned if the logical test is TRUE
value_if_false – the value returned if the logical test is FALSE
Using EOMONTH With The IF Function.
So, this sounds like we can use this along with our EOMONTH and test if the date in Column B is the last day of the month in Excel. I already have the EOMONTH Function in my cell. I just need to wrap the IF Function around it.
The result of the function maybe looks a bit weird. We just need to reformat the cell as Currency.
I can then drag the formula down the column. You can see that Excel correctly inserts 100 Euros in the correct cell relating to the last day of the month. This function will work over and over again as more dates are added to the data set.
The IF an EOMonth function in Excel is a handy tool to have in your arsenal when you need to quickly find the last day of the month. With just a few clicks, you can use this function to determine the last day of any given month. Whether you’re tracking deadlines or trying to plan out events on specific days of the month, using the IF an EOMonth function will save you time and hassle. If you want to learn more about how this function works, or if you’re looking for other tips and tricks for working with dates and times in Excel, sign up for our free Newsletter for more free Excel tips.
