Presentation of the functions 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 month of a date
- YEAR extracts the year of a date
Individually, these functions are not interesting. But used with the DATE function, they are powerful for building dynamic dates 😎👍
How to use the DATE function
The DATE function will return a date with the combination of the 3 arguments
- Year
- Month
- Day
For example, if you want to display the date 31/12/2021, you write the following function:
=DATE(2021, 12, 31) => 31/12/2021
Of course, there is no reason to write a date with the example I have used.
WAIT! It's really important.
But now, instead of the value 31, let's write the DATE function with the value 32 for the days.
=DATE(2021, 12, 32) => 01/01/2022
Of course, no month has 32 days. So, why the formula doesn't return an error?
So how the DATE function works
Behind the scene
We have seen in this article that a date is just a formatted number.
If we change the number format to General, we have the following result
Here, the value 44561 means the 44561 days of Excel because the first date in Excel is the 01/01/1900.
So, even if the arguments don't reflect a real date, the DATE function will always display a correct date according to each value.
Another example with a "ridiculous" month value. Here, we build a date with 17 months
Again, the function has calculated the correct date according to the arguments. We have used this technique to calculate the ending date of the contract duration in this example.
Replace the argument by references
Instead of a value, we can use the contain of a cell's reference to build a date.
Like this, it's easier to change one of the arguments of the date like we did for the calendar.
But also, it's the best way to calculate custom date like the first or the last day of the month
Scot Parker
12/01/2021 @ 00:22
I just want to automate the next day. B3 has the date. B4 has = B3+1. Doesn't work. The cells have been preformated to date.
12/01/2021 @ 08:32
It should. The only reason it doesn't work it's because you don't have a date in B3 or the format of B4 is Text
30/09/2020 @ 05:13
How do I get the column to read the day of the week, then the date of that day, then the month and then the year... as in Wednesday 30 September 2020 and then the cell below to read Thursday 1 October 2020 and so on, in a column down the page ....
Thanks,
26/07/2020 @ 15:31
i have declared the data type of the cell to be date. but whem ever i just enter the day and press enter it shows like this
say 26 then enter
output 26-01-1900
I want excel to display it as 26-07-2020, that is when ever i type the date, i want excel to fill the current month and year for me.
How to achieve it ??
26/07/2020 @ 16:32
Excel returns 26-01-1900 because the first date in Excel is 01-01-1900. So if you fill 26 with a date format, it's logic to display 26-01-1900.
But what you want to do, it isn't possible
20/06/2020 @ 19:07
Hi.. i want to create an attendance register.. but my days should start from the 15th of the previous month, to the 15th of the current month.. what function must i use.
i have used: 2 functions
start date: =DATEVALUE("15"&L1) and end date: =EDATE(D2,1)
But the above functions will only give me dates from the 15th of the current month, to the 15th of the next month, and that's not what i want..
Please hel
20/06/2020 @ 19:26
Hi Paula,
In this article, I have explained how to build the formula to return the first day of any month. Please, read carefully the article and you will see the formula is not difficult.
Then, if you are able to return the first day, you will be able to create the formula for the 15th
14/10/2019 @ 10:13
This is Joshua
I want year and moth and day
18/07/2019 @ 08:34
Sir I want to deduct total days of EOL availed by an employee for example 663 days (1 year 9 months and 24 days) from his total service (41 years 8 months and 17 days), I am unable to do it, please provide a solution to it, if possible.
23/07/2019 @ 07:12
It's possible with the function DATEDIF
06/08/2018 @ 16:39
6/1/2018 Fri
6/17/2018 Sun
how to extract only day from above.....I tried left, right & mid but forward slash is coming as well.
22/04/2018 @ 06:29
I want to make a salary calculator( in excel ) in which I want to add 3% increment in "basic". My columns are :---- 1. "Month"=march 2017, April 2017 etc 2. "Basic"=50000 , 3. "D.A." = basic*.07, 4. "H.R.A."= basic*.08, 5. "Gross"= basic+da+hra .
Please help me .