The DAY function extracts the day of a date
The MONTH function extracts the day of a date
The YEAR function extracts the day of a date
The DATE function is, for its part, creating a date from three parameters that are to be provided and respectively in this order:
For example, if you want to display the date 25/12/2010, you write the following function:
The DATE function is gorgeous because if you write 14 months and 38 days in the second and third parameters, the function will adjust itself the result by adding the days equivalent to 14 months and 38 days. And not only the function returns exactly the number of days depending on the month but also the breakdown of months and years.
You can also override parameters by functions. If you want to always display the current year as the first parameter, you will extract the current year and the functions YEAR and TODAY
First day of a month
It is very easy to create the first of any month, you can just put 1 in the formula DATE third.
For the month and year of the formula DATE, we will use functions MONTH, YEAR TODAY and to extract the month and the current year.
First day of previous month
In this case, you must use exactly the same formula as above and subtract 1 month
Last day of the current month
We all know that the number of days in a month varies. Seen this way, the problem seems very complicated to solve to return the value of the last day of a month. But in fact, thanks to the DATE function, the problem is solved without difficulty.
Let the formula allowed us to calculate the first day of any month. By adding additional one month, we get the first day of the month.
And using the rule of Excel that 1 is equal to one day, you just have to subtract 1 to this formula to find the last day of any month.
Last day of previous month
To display the last day of previous month, simply subtract 1 to the formula that gives you the first day of the month.
To go a little further (and also to impress your friends 🙂 ), you see that your formula ends with 1 and then you subtract 1. So if we replace the 1 – 1 of the formula with a 0, you find that the result is the same.
Now we will use these formulas and the TEXT function to design a document that will display for each month, the first and last day of the previous month.
This example is very important because every month, a lot of managers change dates of the timesheet manually and they loose a lot of time. Now, it’s Excel which do this task for you.