Cleaning Data Containing Date Values

The data that you obtain from different sources might contain date values. In this chapter, you will understand how to prepare your data that contains data values for analysis.

You will learn about −

• Date Formats
  • Date in Serial Format
  • Date in different Month-Day-Year Formats
• Converting Dates in Serial Format to Month-Day-Year Format
• Converting Dates in Month-Day-Year Format to Serial Format
• Obtaining Today's Date
• Finding a Workday after specified Days
• Customizing the Definition of a Weekend
• Number of Workdays between two given Dates
• Extracting Year, Month, Day from Date
• Extracting Day of the Week from Date
• Obtaining Date from Year, Month and Day
• Calculating Number of Years, Months and Days between two Dates

Date Formats

Excel supports Date values in two ways −

• Serial Format
• In different Year-Month-Day Formats

You can convert −

• A Date in Serial Format to a Date in Year-Month-Day Format

• A Date in Year-Month-Day Format to a Date in Serial Format

Date in Serial Format

A Date in serial format is a positive integer that represents the number of days between the given date and January 1, 1900. Both the current Date and January 1, 1900 are included in the count. For example, 42354 is a Date that represents 12/16/2015.

Date in Month-Day-Year Formats

Excel supports different Date Formats based on the Locale (Location) you choose. Hence, you need to first determine the compatibility of your Date formats and the Data Analysis at hand. Note that certain Date formats are prefixed with *(asterisk) −

• Date formats that begin with *(asterisk) respond to changes in regional date and time settings that are specified for the operating system

• Date formats without an *(asterisk) are not affected by operating system settings

For understanding purpose, you can assume United States as the Locale. You find the following Date formats to choose for the Date - 8^th June, 2016 −

• *6/8/2016 (affected by operating system settings)
• *Wednesday, June 8, 2016 (affected by operating system settings)
• 6/8
• 6/8/16
• 06/08/16
• 8-Jun
• 8-Jun-16
• 08-Jun-16
• Jun-16
• June-16
• J
• J-16
• 6/8/2016
• 8-Jun-2016

If you enter only two digits to represent a year and if −

• The digits are 30 or higher, Excel assumes the digits represent years in the twentieth century.

• The digits are lower than 30, Excel assumes the digits represent years in the twenty-first century.

For example, 1/1/29 is treated as January 1, 2029 and 1/1/30 is treated as January 1, 1930.

Converting Dates in Serial Format to Month-Day-Year Format

To convert dates from serial format to Month-Day-Year format, follow the steps given below −

• Click the Number tab in the Format Cells dialog box.

• Click Date under Category.

• Select Locale. The available Date formats will be displayed as a list under Type.

• Click on a Format under Type to look at the preview in the box adjacent to Sample.

After choosing the Format, click OK.

Converting Dates in Month-Day-Year Format to Serial Format

You can convert dates in Month-Day-Year format to Serial format in two ways −

• Using Format Cells dialog box

• Using Excel DATEVALUE function

Using Format Cells dialog box

• Click the Number tab in the Format Cells dialog box.

• Click General under Category.

Using Excel DATEVALUE Function

You can use Excel DATEVALUE function to convert a Date to Serial Number format. You need to enclose the Date argument in “”. For example,

=DATEVALUE ("6/8/2016") results in 42529

Obtaining Today's Date

If you need to perform calculations based on today’s date, simply use the Excel function TODAY (). The result reflects the date when it is used.

The following screenshot of TODAY () function usage has been taken on 16^th May, 2016 −

Finding a Workday after Specified Days

You might have to perform certain calculations based on your workdays.

Workdays exclude weekend days and any holidays. This means if you can define your weekend and holidays, whatever calculations you do will be based on workdays. For example, you can calculate invoice due dates, expected delivery times, the next meeting date, etc.

You can use Excel WORKDAY and WORKDAY.INTL functions for such operations.

For example, you can specify the 15^th working day from today (the screenshot below is taken on 16^th May 2016) using the Functions TODAY and WORKDAY.

Suppose 25^th May 2016 and 1^st June 2016 are holidays. Then, your calculation will be as follows −

Customizing the Definition of a Weekend

By default, weekend is Saturday and Sunday, i.e. two days. You can also optionally define your weekend with the WORKDAY.INTL function. You can specify your own weekend by a weekend-number that corresponds to the weekend days as given in the table below. You need not remember these numbers, because when you start typing the function, you get a list of numbers and the weekend days in the drop-down list.

Suppose, if weekend is Friday only, you need to use the number 16 in the WORKDAY.INTL function.

Number of Workdays between two given Dates

There might be a requirement to calculate the number of workdays between two dates, for example, in the case of calculating payment to a contract employee who is paid on per day basis.

You can find the number of workdays between two dates with the Excel functions NETWORKDAYS and NETWORKDAYS.INTL. Just as in the case of WORKDAYS and WORKDAYS.INTL, NETWORKDAYS and NETWORKDAYS.INTL allow you to specify holidays and with NETWORKDAYS.INTL you can additionally specify the weekend.

You can calculate the number of workdays between today and another date with the functions TODAY and NETWORKDAYS. In the screen shot given below, today is 16^th May 2016 and end date is 16^th June 2016. 25^th May 2016 and 1^st June 2016 are holidays.

Again, the weekend is assumed to be Saturday and Sunday. You can have your own definition for weekend and calculate the number of workdays between two dates with the NETWORKDAYS.INTL function. In the screen shot given below, only Friday is defined as weekend.

Extracting Year, Month, Day from Date

You can extract from each date in a list of dates, the corresponding day, month and year using the excel functions DAY, MONTH and YEAR.

For example, consider the following dates −

From each of these dates, you can extract day, month and year as follows −

Extracting Day of the Week from Date

You can extract from each date in a list of dates, the corresponding day of the week with Excel WEEKDAY function.

Consider the same example given above.

Obtaining Date from Year, Month and Day

You data might have the information about Year, Month and Day separately. You need to get the date combining these three values to perform any calculation. You can use the DATE function for getting the date values.

Consider the following data −

Use the DATE function to obtain DATE values.

Calculating Years, Months and Days between two Dates

You might have to calculate the time lapsed from a given date. You might need this information in the form of years, months and days. A simple example would be calculating the current age of a person. It is effectively the difference between the birth date and today. You can use Excel DATEDIF, TODAY and CONCATENATE functions for this purpose.

The output is as follows −