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.

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**.

## 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 13^{th} 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 Format | U.S. | Elsewhere in the world |
---|---|---|

10/5/2016 | 5th October 2016 | 10th 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.

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.

Date | Output | Format Information |
---|---|---|

15 August 2016 | 15/08/2016 | Short Date Format |

15 August 2016 | Monday, 15 August 2016 | Long Date Format |

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

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**.

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

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.

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.

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

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.

Code | Description | Output Format |
---|---|---|

m | Months as either single or double digit | 1-12 |

mm | Months as a double-digit starting a leading zero if required | 01-12 |

mmm | Months in a three letter short form | Jan-Dec |

mmmm | Months full name | January-December |

mmmmm | Months as the first letter of the month | J, F, M, A, M, J, J, A, S, O, N, D |

d | Days as either s single or double digit | 1-31 |

dd | Days as a double digit starting a leading zero if required | 01-31 |

ddd | Days in a three letter short form | Sun-Sat |

dddd | Days full name | Sunday-Saturday |

yy | Years as a double digit starting short format | 00-99 |

yyyy | Years as a four digit format | 1900-9999 |

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

The following table is a list of example date formats.

Date | Output | Format Information |
---|---|---|

15 August 2016 | 15-Aug-16 | d-mmm-yy |

15 August 2016 | 15-08-16 | d-mm-yy |

15 August 2016 | 15-08-2016 | dd-mm-yyyy |

15 August 2016 | Aug-16 | mmm-yyyy |

15 August 2016 | 2016-08-15 | yyyy-mm-dd |

15 August 2016 | 08-15-16 | mm-dd-yy |

15 August 2016 | Monday, August, 15, 2016 | ddddd, mmmm, dd, yyyy |

15 August 2016 | Mon, 15, August, 16 | ddd, dd, mmmm, yy |

15 August 2016 | 15, Aug, 2016 | dd, 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.

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!

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.

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

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)**.

Parameter | Description |
---|---|

year | The 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 |

month | The 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. |

day | The 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**.

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

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.

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.

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.

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.

#### 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**.**

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

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.

Like before, we insert the formula into a cell.

#### 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.

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

### 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)**.

Parameter | Description |
---|---|

end_date | end_date parameter is simply the end date. This parameter is required. |

start_date | start_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**.

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

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.

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.

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.