It’s no surprise that Excel is often used for time based reporting, whether analyzing trends over time, creating a financial model or just analyzing your own personal budget. There are over 20 Date and Time functions in Excel’s library to handle every imaginable type of date/time calculation. Here are two of the most important.

The DATEDIF Function

This function finds the amount of time in days, months, or years that are in between two dates

This function takes three arguments:

1. Start Date
2. End Date
3. Year, Month, or Days between the two dates (y, m, d)

In the above example we have two dates: 1/1/2010 and 5/21/2019. We’d like to find the time that has passed between the two dates.

The formulas are shown in the third columns and the results of the formulas shown in the far right. There have been

• 9 calendar years between the two dates
• 112 calendar months
• 3,427 days

The DATE Function

This function takes three arguments:

1. Year
2. Month
3. Day

Think of this function as constructing a date by hand using the raw ingredients. The times that I have found this most helpful is either when I have to clean up data that is formatted incorrectly and pull out the correct date, or when I need to dynamically generate dates in the future/past based on inputs already in a workbook.

I often use the function in conjunction with other functions depending on the data I’m working with, but the result is the same; a usable date.

Example 1: Dynamically Creating a Date in the Future (or Past)

I’m going to introduce you to a few more functions as part of this DATE function tutorial. While it may seem like a lot to take, I promise it will be worth it to learn

YEAR, MONTH, and DAY Functions

These functions do exactly what you’d expect them to do; pull out the year, month, and day from a calendar date.

YEAR Function

The YEAR of the date is 2020, so that is the result of the function.

MONTH Function

The MONTH of the date is March. Since it is the 3rd month of the year, the result is 3.

NOTE: To return the word “March” as a result of pulling out the month of the function, I’d suggest using the TEXT function.

DAY Function

The result of the DAY function is the numbered day within the month. Since we’re looking at March 4th, the result of the DAY function is 4.

Note: If you’re trying to pull out the day of the week, ie ‘Monday’, again I’d suggest using the text function and pulling out the info that way.

Now that we understand the YEAR, MONTH, and DAY functions, let’s apply those back to the DATE function.

In the formula above I’ve used DATE, YEAR, MONTH, and DAY together. In the last argument (day), I’ve added one to the day of the month. As a result, we get “1/2/2020”. We can do the same thing for Year and Month as well:

We may be creating a financial model, making projections into the future, or merely creating a calendar. In any case, the DATE function allows you to pull together the month, day, and year to create the date YOU need. Isolating one or more of these arguments is often a big time saver.

 

Example 2: Reformatting Date information stored as Text

Once again I’d like to introduce you to a few functions that will help get the most out of the DATE function.

The LEFT, RIGHT, and MID Functions

These functions are used to pull information from the LEFT, RIGHT, and middle (MID) of a string. When we encounter a date we know is stored as a string, these functions along with DATE can help us right the ship.

The LEFT Function

Notice in the picture above we have a date that is not formatted correctly. It’s saved as a string with these characteristics:

• The month is stored in the first two digits
• The day is stored in the middle two digits
• The year is stored in the last four digits

The LEFT function takes two arguments:

1. Text to extract from
2. How many characters to pull

In the picture above, the result of our LEFT formula is ’01’ because those are the first two digits in the string. Later on, that will be used to map the month of January

The MID Function

The MID function takes three arguments:

1. Text to extract from
2. Where to start inside the string
3. How many characters to pull

In the picture above, we’ve started at the 3rd character in the string and removed two characters starting from that point. Thus, our result is ’01’. That will be used to map the DAY of 1 within January.

The RIGHT Function

The RIGHT function is very similar to the LEFT function and takes two arguments:

1. Text to extract from (starting from the right)
2. How many characters to pull

In the picture above, we’ve pulled four characters from the right side of the string, which will ultimately help us map the YEAR of the date.

Let’s autofill that down across our entire dataset:

Putting it all together again with the DATE Function

 

Here we’re using the LEFT, RIGHT, and MID functions to pull out the month, year, and day from the string. If we place these strings inside of the DATE function, the result is an actual date that we can use (column J) instead of a long text string. Not you can extract insights, create pivot tables and charts, and move on with your analysis. Enjoy.