- Excel Data Analysis Tutorial
- Excel Data Analysis - Home
- Data Analysis - Overview
- Data Analysis - Process
- Excel Data Analysis - Overview
- Working with Range Names
- Tables
- Cleaning Data with Text Functions
- Cleaning Data Contains Date Values
- Working with Time Values
- Conditional Formatting
- Sorting
- Filtering
- Subtotals with Ranges
- Quick Analysis
- Lookup Functions
- PivotTables
- Data Visualization
- Data Validation
- Financial Analysis
- Working with Multiple Sheets
- Formula Auditing
- Inquire

- Advanced Data Analysis
- Advanced Data Analysis - Overview
- Data Consolidation
- What-If Analysis
- What-If Analysis with Data Tables
- What-If Analysis Scenario Manager
- What-If Analysis with Goal Seek
- Optimization with Excel Solver
- Importing Data into Excel
- Data Model
- Exploring Data with PivotTables
- Exploring Data with Powerpivot
- Exploring Data with Power View
- Exploring Data Power View Charts
- Exploring Data Power View Maps
- Exploring Data PowerView Multiples
- Exploring Data Power View Tiles
- Exploring Data with Hierarchies
- Aesthetic Power View Reports
- Key Performance Indicators

- Excel Data Analysis Resources
- Excel Data Analysis - Quick Guide
- Excel Data Analysis - Resources
- Excel Data Analysis - Discussion

- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who

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

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

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.

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.

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

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

Using

**Format Cells**dialog boxUsing Excel

**DATEVALUE**function

Click the

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

**General**under**Category**.

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

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 −

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 −

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.

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.

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 −

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.

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.

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 −

Advertisements