Introduction to Excel Date Functions Part 1

Introduction to Excel Date Functions Part 1 Twitter

Having to work with dates is common activities, Excel provides numerous functions to help us quickly get the information or outcome you are looking for. You might want to update a value based on today’s date, work out the number of days, months, or years between two dates, or calculate someone’s age. All of this and more is easy to do in Excel.

In this three-part series of blog posts, I am going to show you how to output dates in different formats and walk you through some of the most commonly used date functions in Excel.

In the first blog post, we’re going to come to grips with how to format dates differently and have a look at a few useful and commonly used date functions to get you started.

A Quick Bit of Background on How Dates Work

In order to prevent frustration when working with dates, it’s worth taking five minutes to understand how Excel handles dates.

Microsoft Excel doesn’t actually store a date; what it actually stores is a serial number. To explain this a little further, we have to go back in time. At some point, someone decided that all dates in Excel would start from the date 0 January 1900. Obviously, 0 January 1900 isn’t an actual date, but all dates in Excel start from 0 January 1900. When you want to enter a date into Excel, what you are really entering is a serial number that calculates the number of days since 0 January 1900.

That’s why you can switch between one date format and another without too much of an issue.

In order to demonstrate this point, let’s enter the date I’m writing this blog post into a cell. In cell A3, type “15 August 2016,” and let’s make sure it is formatted as a date.

Date dd mmmm yyyyy

Now, copy the date to cell B3, select the Formatting drop-down menu, and select the General option. When you do this, you should see the date’s serial number of 42597.

Date Serial Number as a Date and General

Entering Dates

If you know the serial number of the date you want to enter, you could type it directly into a cell, click Format as a Date, and your date will be displayed. I highly recommend not using this method!

Providing you use a valid date format, Excel will calculate the date’s serial number you enter and display it using your chosen date format. The date format can then be changed as required.

Date formats can change depending on the regional settings of Excel and your computer. Care should be taken when entering dates, as some dates can cause confusion; specifically, the placement of the month and day in a date when the day is prior to the 13th of a month. In the U.S., the format that is most often used is month/day/year, whereas elsewhere in the world, the format day/month/year is used.

Date FormatU.S.Elsewhere in the world
10/5/20165th October 201610th May 2016

Formatting Dates

The extent to which you can format dates is almost endless. Excel has a couple of predetermined formats that you can access quickly from the cell Format drop-down menu on the Home tab of the ribbon.

Predetermined date formats

Using a base date of the 15 August 2016, the following table shows you how the date would be formatted using the Short Date Format and Long Date Format from the drop-down menu.

DateOutputFormat Information
15 August 201615/08/2016Short Date Format
15 August 2016Monday, 15 August 2016Long Date Format

A more extensive method of formatting dates can be accessed by right-clicking on a cell and clicking Format Cell.

Right click menu

This displays the Format Cell dialogue box. In the Category pane of the Format Cell box, there are two categories to choose from: Date and Custom.

Format Cell dialogue box

The Date category is a list of built-in date formats based on the selected Location drop-down menu.

Format Cell dialogue box - Date Catergory

This options is fairly straightforward. When you select a date format, then click OK on the Format Cell dialogue, the range of cells selected is formatted using the selected date format.

Format Cell Dialogue Date Catergory Formats

Remember the earlier comment about entering dates and the confusion regarding the day and month placement in the date format. The Location drop-down menu in the Format Cell dialogue can affect the date format.

Format Cell Dialogue Change Location

The Custom category of the Format Cell dialogue is much more flexible but also requires some explanation.

Format Cell Dialogue Custom Date Format

Dates are formatted using a series of letters and separation characters. The characters d, m, and y are used to represent the day, month, and year. The commonly used separation characters are the dash, backslash, and comma. In combination, these characters are used to build a date format.

The following table describes the different day, month and year formats you can use and their associated output format.

CodeDescriptionOutput Format
mMonths as either single or double digit1-12
mmMonths as a double-digit starting a leading zero if required01-12
mmmMonths in a three letter short formJan-Dec
mmmmMonths full nameJanuary-December
mmmmmMonths as the first letter of the monthJ, F, M, A, M, J, J, A, S, O, N, D
dDays as either s single or double digit1-31
ddDays as a double digit starting a leading zero if required01-31
dddDays in a three letter short formSun-Sat
ddddDays full nameSunday-Saturday
yyYears as a double digit starting short format00-99
yyyyYears as a four digit format1900-9999

An example of a custom date format is d-mmm-yy. This would output dates in the following format.

date format d-mmm-yy

The following table is a list of example date formats.

DateOutputFormat Information
15 August 201615-Aug-16d-mmm-yy
15 August 201615-08-16d-mm-yy
15 August 201615-08-2016dd-mm-yyyy
15 August 2016Aug-16mmm-yyyy
15 August 20162016-08-15yyyy-mm-dd
15 August 201608-15-16mm-dd-yy
15 August 2016Monday, August, 15, 2016ddddd, mmmm, dd, yyyy
15 August 2016Mon, 15, August, 16ddd, dd, mmmm, yy
15 August 201615, Aug, 2016dd, mmm, yyyy

Some Functions to Get You Started

The following functions will get you started using dates in formulas. We’ll build on these functions in the next blog post in this series introducing date functions.

The TODAY Function

The TODAY function returns the current date, so whenever you open an Excel workbook that contains the TODAY function, it will return the current date. If you have formulas or calculations dependent on the current date, this can be very useful.

The TODAY function has no parameters; therefore, the syntax for the TODAY function simply is TODAY().

A simple example of the TODAY function is to type the following formula in a worksheet.

TODAY Function

When you do, it will return the current date like the image below. Note, the date on the image below was correct at the time of writing this blog, it will be incorrect; it’s no longer the current date!

TODAY Function in cell B2 v2

How could this be useful? Well, you might want to know what the date will be in 30 days’ time. To do this, we will use the following formula.

TODAY Function plus 25

When done, assuming that the current date is 19th August 2016, the worksheet will look like the image below.

TODAY Function in cell B2 plus 25

The TODAY function will be used in the next blog post, when we are working out someone’s age.

 The DATE Function

The DATE function can be used to create a valid date without the need to type it into a cell. The DATE function takes three parameters: the year, the month, and the day. The main benefit of the DATE function is that you can use it to manipulate dates in much the same way as you can any other value in a formula.

The syntax of the DATE function is DATE (year, month, day).

ParameterDescription
yearThe year parameter is a positive whole number (integer); negative numbers are not valid and will return an error. The year has to be one to four digits long. It is best to use four digits to represent the year; otherwise, the DATE function will add 1, 2, and 3 digit numbers to the year 1900. Remember that Excel calculates dates as serial numbers starting in the year 1900
monthThe month parameter is a whole number (integer), either positive or negative, representing the month of the year from 1 to 12.
There are a couple of things to remember about the month parameter:
·      If the parameter value is greater than the number of months in a year, then the number of months is added to the year specified. For example, DATE(2016,14,1) is 14 months, 2 more months than is available in a year, returns the serial number representing 1 February 2017.
·      If the parameter value is a negative number, then the number of months plus one is subtracted from the first month of the year specified. For example, DATE(2016,-10,1) returns the serial number representing 1 February 2015.
dayThe day parameter is a whole number (integer), either positive or negative, representing the day of the month from 1 to 31.
There are a couple of things to remember about the day parameter:
·      If the parameter value is greater than the number of days in the month specified, then the additional number of days is added to the following month. For example, DATE(2016,8,40) is 9 days more than available in August and returns the serial number representing 9 September 2016.
·      If the parameter value is a negative number, then the number of days in the month specified is subtracted from the previous month. For example, DATE(2016,8,-10) returns the serial number representing 21 July 2016.

Entering a Straightforward Date

In the first example, we will just enter a straightforward date. Start by entering the following formula in cell B2.

 

Date Function

When the formula is entered, it will return the date that this blog post was written.

Date Function cell B1

As with other functions, we could also use cell references; to do this, let’s modify the previous formula with some cell references using the formula below.

Date Function cell reference

Once we’ve modified the formula, we need to input values into cells B4, B5, and B6 for the year, month, and day respectively, as shown in the image below.

Date Function cell reference in B6

The formula still outputs a date, but now we can adjust the date by entering different values in cells B4, B5, and B6.

What Was the Date 25 Days Ago?

Okay, now let’s do something useful with this formula. What if you want to know what day it was 25 days ago? In order to do this, we simply subtract 25 from the value in the day parameter of the DATE function. To do this, we modify the formula as seen below.

Date Function cell reference in B6 -25

As you can see, the reference to cell B6 has been modified so that we’re taking 25 away from the value in that cell.  We’ll move this new formula into cell 10. Worksheet now looks like the image below.

Date Function cell reference in B6 -25 B8

What Will the Date Be in 63 Days?

We can use exactly the same method as before to find out what date is in 63 days’ time by modifying the formula as shown in the image below.

Date function cell reference +63

Again, when we insert the formula into the worksheet, we get the date in 63 days’ time.

Date function cell reference +63 in B10

Notice, in both the previous examples, the number of days added and taken away from the day value caused a change in the month. This can also be used to cause a change in the year.  In this next example, we will add 533 days to the value in the day parameter. To do this, we will modify the formula like before, simply adding 533 to the day parameter value.

Date function cell reference +533

Like before, we insert the formula into a cell.

Date function cell reference +533 B14

A Real World Example

The following example uses the date function to calculate the final payment date of the invoice with payment terms of 45 days.  We’re going to use a similar formula as before to work out the invoice date, referencing three cells to hold the year, month, and day values.

Then, a second cell will hold the value that represents the payment terms in number of days, and a third cell will display the payment due date based on the invoice date and payment terms.

To achieve this, we use the previous formula, simply correcting the date using references to 3 cells as before.

The second formula will be a modified version of the first formula, but we will simply add the payment terms value to the day parameter of the DATE function using the formula below.

Date function cell reference payment terms to date

Using these two formulas, the worksheet now looks like the image below.

Date function cell reference payment terms to date C16

The DAYS Function

The DAYS function is used to return the number of days between two dates, start date and end date.

The syntax of the DAYS function is DAYS(end_date, start_date).

ParameterDescription
end_dateend_date parameter is simply the end date. This parameter is required.
start_datestart_date parameter is simply the start date. This parameter is required.

The DAYS function can accept and recognize dates as a text string, so long as they are in recognized date format. However, if the text string isn’t a valid date format, the DAYS function will return a #VALUE! error.

 Using the DAYS Function

For our first example, we will use the DAYS function to calculate the number of days between an invoice being issued to a client and payment being made.

To do this, we will use three cells, “Date invoice issued” in cell A4, “Day payment made” in cell B4, and the “Days” between the two in cell C4. To calculate the number of days between the two dates, we will insert the following formula into cell C4.

DAYS Function

Once we insert the formula and add a couple of dates to cells A4 and B4, the worksheet will look like the image below.

DAYS Function cell C4

From this image, we can see that it took 45 days for the client to pay the invoice.

Using the DAYS Function to See if a Payment Is Overdue

Okay, let’s extend our first example and add a few more cells to identify if a payment is overdue.

We’ll use the same worksheet as a comparison, so this time, we will have 5 cells: “Date invoice issued” in cell A8, “Day payment made” in cell B8, “Actual Days” in cell C8, “Payment Terms” in cell D8, and “Payment” in cell E8.

The two new cells here are “Payment Terms” in cell D8, which is used to identify the number of days within which an invoice should be, and “Payment” in cell E8, which will calculate and display whether a payment was on time or late using the following formula.

DAYS Function Payment Days

Notice that the formula also uses the IF function. For more on the IF function, please click here. Once we insert the formulas, add a couple of dates to cells A8 and B8, and set the payment terms to 30 days in cell, the worksheet will look like the image below.

Invoice Payment Terms

It looks like the client was late in paying the invoice.

It’s not hard to imagine using both the DATE and DAYS functions in a workbook that is used to track the raising of invoices and client payments within the payment terms.

Wrap-up

Okay, that’s it for this post. As you can see, there’s more to dates in Excel than might first meet the eye. However, once you’ve got your head around how Excel stores dates and a few basic Excel date functions, you can achieve quite a lot.

In the next post, we’re going to look a lot some more Excel date functions that can used to calculate the number of working days between two dates, taking into account weekends and holidays. How to calculate someone’s age and other useful Excel date functions.

If you liked this post, please share it with your friends on Facebook or Twitter.

For other tools and resources for learning more about Excel, check out my resources page.

To get a copy of the example spreadsheet used in this blog post and receive a copy of our newsletter, sign up to our email list.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit