Excel contains special functions that will let you extract the day, month, and year from a valid date.
Let's take a look.
Here we have a set of random dates in column B. First, I'll add a formula to column C to pick up the date values in B and format them in the general format so we can see the value. You can see these are are normal date serial numbers, and B11 is the only cell that contains a time value.
Using built-in functions, I'll extract the year, month, and day from each date. This can be useful if you want to break apart a date, manipulate certain components, and recombine the date again.
First, I'll extract the day value using a function called DAY. Day takes one argument, a date in serial number format. When I give it the value in column C, it returns just the day from the date.
To get month, I'll use the MONTH function. Like the DAY function, MONTH requires only the date and will return the month number of the date.
Finally, the YEAR function will return just the year.
All of these functions or fully dynamic and will calculate a new result if a date value changes.
Now let's recombine these date components into a date again. For that, we need to use the DATE function. DATE takes year, month, and day as separate arguments and returns a valid date.
Since we have all of these values exposed in the worksheet, I can easily add them to the formula and copy it down.
You can see that when you have dates broken into their components, you are free to change those components before they are reassembled into a date.
For example, you can easily add 30 days to date. Or 60 days.
Note that Excel takes care of changing the month for me.
Likewise, I can add 12 months to a date and Excel will update the year. If I add 36 months to the date, Excel just rolls the year forward as needed.
Working with dates this way gives you a lot of flexibility. For example, what if I wanted to keep the month and day for these dates, but use a common year?
No problem, I can just hard code any year I like and reuse the month and day.
The Excel DAY function returns the day of the month as a number between 1 to 31 from a given date. You can use the DAY function to extract a day number from a date into a cell. You can also use the DAY function to extract and feed a day value...
The Excel MONTH function extracts the month from a given date as number between 1 to 12. You can use the MONTH function to extract a month number from a date into a cell, or to feed a month number into another function like the...
The Excel YEAR function returns the year component of a date as a 4-digit number. You can use the YEAR function to extract a year number from a date into a cell or to extract and feed a year value into another formula, like the...
The Excel DATE function creates a valid date from individual year, month, and day components. The DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.