Exceljet

Quick, clean, and to the point

How to work with dates

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.

Course 
Core Formula

Related shortcuts

CtrlZ
Z
CtrlEnter
Return
Author 
Dave Bruns