Presentation of DAY, MONTH, YEAR
The Functions YEAR, MONTH and DAY are very simple to understand. They extract each part of a date.
- DAY extracts the day of a date
- MONTH extracts the day of a date
- YEAR extracts the day of a date
Individually, these functions do not interesting but used with the DATE function, they are powerful to build dynamic dates.
The DATE function, the link
The DATE function need the 3 parameters of a date to build a date
For example, if you want to display the date 31/12/2017, you write the following function:
=DATE(2017, 12, 31)
Hmmmm, OK! 🤔🤨 But what is interest ? I can write directly 31/12/2017 in a cell without this function.
True! But the interest of this function is to replace the value 2017, 12 or 31 by variables. Let's see some examples.
Convert date format without formula
If your need is to convert date format , like YYYYMMDD to DD/MM/YYYY, it is not necessary to use this formula.
You can do it with the tool Text to Columns. You will find the explanations in this article.
Let's say you want to create a date that will display always the first day of the current month. For that we will use the function TODAY.
If we replace in the previous pics the value in B1 by TODAY, we have now this result
It may looks easy, stupid, no interest. But whatever you will use these formulas, it will always return the day, the month or the year of the current day 😍😍😍
First day of the current month
It is very easy to create a dynamic date for the first day of any month.
The first 2 parameters of the DATE function are the current year and current month
And now, what is the parameter for the day? Well, very easy ... it's 1 🤣😋😉
So the formula to return the first day of the current month is
First day of the previous month
With the same logic, if you want to display always the first month of the previous month the formula is the same. The only thing that is different it's the fact to reduce the value of the month by 1
And it works even if you overlap a year. Look at this different result
Last day of the previous month
A lot of users think that create a dynamic date for the last day of any month it's a nightmare. Not at all.
Let's rewrite the formula for the first day of the current month
In Excel, 1 is one day (and never one hour). So we just have to subtract 1 to the formula to return the last day of the previous month
If you want to show that you are an Excel pro to your friends, you can replace the 1-1 in the formula by 0. The result is the same
Last day of the current month
On the same way, if you want to return the last day of the current month, we have to build a formula to display the first month of the next month
And subtract 1 one more time.
- Change the color of the weekends
- Weekly calendar 2020 in one formula
- Difference between 2 dates – DATEDIF
- How to Highlight a Birthday Automatically
- Convert YYYYMMDD to DD/MM/YYYY
- First day – Last day in Excel
- Date Format in Excel
- Convert Coordinates Latitude and Longitude
- The Correct Value for a Week Number
- Add Days Excluding the Weekend