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 with the General format, so we can see the raw value. You can see that these 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 just one argument: a valid date in serial number format. When I give it the value in column C, it just returns the day from the date.

To get the month value, I need to use the MONTH function. Like the DAY function, MONTH requires only the date and returns the month value from the date.

Finally, the YEAR function will return just the year.

Notice that all of these functions are 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, I need to use the DATE function. DATE takes year, month, and day as separate arguments and returns a valid date.

Since I already have all of these values exposed on 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're free to change these components before they get reassembled into a date.

For example, you can easily add 30 days to a date. Or 60 days. Notice that Excel takes care of changing the month for me.

Likewise, I can add 12 months to a date and Excel will automatically update the year. If I add 36 months to a date, Excel just rolls the year forward as needed.

When you work with dates this way, you have 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.