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 compatibipty 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 −

Cpck the Number tab in the Format Cells dialog box.

Cpck Date under Category.

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

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

After choosing the Format, cpck 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

Cpck the Number tab in the Format Cells dialog box.

Cpck 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 hopdays. This means if you can define your weekend and hopdays, whatever calculations you do will be based on workdays. For example, you can calculate invoice due dates, expected depvery 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 hopdays. 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 pst of numbers and the weekend days in the drop-down pst.

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

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 pst 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 pst 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 −