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

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

S.No. | Function & Description |
---|---|

1. |
Returns the serial number of the date before or after a specified number of workdays |

2. |
Returns the serial number of the date before or after a specified number of workdays using parameters to indicate which and how many days are weekend days |

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.

Weekend Days | Weekend-number |
---|---|

Saturday, Sunday | 1 or omitted |

Sunday, Monday | 2 |

Monday, Tuesday | 3 |

Tuesday, Wednesday | 4 |

Wednesday, Thursday | 5 |

Thursday, Friday | 6 |

Friday, Saturday | 7 |

Sunday only | 11 |

Monday only | 12 |

Tuesday only | 13 |

Wednesday only | 14 |

Thursday only | 15 |

Friday only | 16 |

Saturday only | 17 |

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.

S.No. | Function & Description |
---|---|

1. |
Returns the number of whole workdays between two dates |

2. |
Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days |

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 −