Welcome to yet another exciting tutorial on our excel series. In this post, we will swiftly discuss the quickest way to deduce the initial day of the month from an excel spreadsheet. As we know, excel is never in short supply of intrigues, so let's proceed!
As already stated, only one function is being present in returning the last day of the month, i.e., the EOMONTH function. But in getting the first day of the month, there are different ways to acquire it.
Example 1. Find the 1st day of the month by month number
You can use an easy DATE formula thus if you have the number of the month:
=DATE(year, month number, 1)
For example, =DATE(2015, 4, 1) will return 1-Apr-15.
The location of your numbers is very important. E.g., if your numbers are situated in Column A, a cell reference can be added in the formula directly:
=DATE(2015, B2, 1)
Example 2. Find the 1st day of the month from a date
Using the Excel DATE function, you can arrive at the first day of the month based on a date, but to achieve this, the MONTH function will be needed to separate the number of the month.
=DATE(year, MONTH(cell with the date), 1)
For instance, the first day of the month will be returned based on the date in cell A2 by using this formula:
=DATE(2015,MONTH(A2),1)
Obtaining the 1st day of the month from a date is somewhat similar to these steps.
Example 3. Obtain the first day of the month based on the current date
When questions come up that demands today's or current date, you make use of the EOMONTH and the TODAY functions together:
=EOMONTH(TODAY(),0) +1 – returns the 1st day of the following month.
In earlier workings, the last day of the current month was gotten by using related formula. In getting the first day of the coming or next month, you just add 1 to that formula.
In a related form, the first day of the past and recent or current month can be acquired:
=EOMONTH(TODAY(),-2) +1 – returns the 1st day of the previous month.
=EOMONTH(TODAY(),-1) +1 – returns the 1st day of the current month.
The DATE function of the Excel Worksheet can also be used to handle other tasks though it would be a little longer. For example
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
Yep, it returns the first day of the current month.
And how do you force it to return the first day of the following or previous month? Hands down 🙂 Just add or subtract 1 to/from the current month:
To return the first day of the following month:
=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)
To return the first day of the previous month:
=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)
Our subsequent tutorials will cover other aspects of this topic. For now, I hope you've learned a lot as regards the topic of discussion in this post. Thanks for reading!