How to use functions to identify and calculate date information in Excel

The following article details how to use functions in Excel 2013 and 2016 to identify and calculate date information.

To use the functions related to date information, let's explore the following:

1. The relationship between Excel and the date is nothing special.

1.1 How is the date related to Excel?

- Different from numeric or text data types . dates are stored by Excel in a specific way and different from above data types. The dates in Excel are stored as text to serial numbers that Excel recognizes as dates. For example, on 01/01/08 -> the serial number stored in Excel is 39448.

- Excel's calendar dates back to 1900 - the day Excel was born. The way to convert dates to Excel serial numbers is calculated as follows: Excel treats January 1, 1900 as 1, and adds 1 to the serial number every 1 day thereafter. Specifically:

How to use functions to identify and calculate date information in Excel Picture 1

1.2 The date formats Excel supports.

- To know the date formats that Excel supports you do the following:

+ Right-click in any cell in Excel -> Format Cells:

How to use functions to identify and calculate date information in Excel Picture 2

+ The dialog box appears in the Number tab -> Date -> formats that Excel supports are in the Type:

How to use functions to identify and calculate date information in Excel Picture 3

+ Excel also supports users to define their own date style based on Excel's suggestions. Click Custom -> the formats that Excel supports are in the Type:

How to use functions to identify and calculate date information in Excel Picture 4

In addition to the Excel format will not be interpreted as a date, it will be treated as a text string or an error message.

2. Use formulas in calculating dates.

Some functions that are commonly used in calculating dates:

How to use functions to identify and calculate date information in Excel Picture 5

2.1 Calculate the number of days between two specific times.

- To calculate the number of days between two specific dates, the end date minus the start date, provided that the date is in the specified format in Excel.

For example:

How to use functions to identify and calculate date information in Excel Picture 6

- Where the end date is less than the start date -> the return value is less than 0.

How to use functions to identify and calculate date information in Excel Picture 7

2.2 Calculate the number of working days between 2 specific times (except weekends and holidays).

- To calculate the number of working days using the NETWORKDAYS () function . For example, calculate the number of working days when you know your start and end dates.

How to use functions to identify and calculate date information in Excel Picture 8

- In case the end date is less than the start date -> the function returns the number of days less than 0:

How to use functions to identify and calculate date information in Excel Picture 9

2.3 Search for 1 day after 1 working day.

- To find a date after 1 working day -> use the Workday function . For example, finding the date of completion of a work, the number of days to complete the work is 120 days and the start date is 10/10/2016, during which time is entitled to 2 holidays: December 10, 2016 and 10/24. / 2017.

How to use functions to identify and calculate date information in Excel Picture 10

2.4 Find a time after 1 month.

To find 1 day after 1 month -> use the Date () function . For example, find the date after 10/10/2016 3 months:

How to use functions to identify and calculate date information in Excel Picture 11

2.5 Find a time after a number of years, a number of months, and a number of specific dates.

- Find the date after 1 number of years, 1 number of months and 1 number of specific days -> using the Date () function . For example, find the date after 10/10/2016 3 years, 9 months and 27 days:

How to use functions to identify and calculate date information in Excel Picture 12

The above are specific instructions and examples for defining information and calculating dates in Excel 2013.

Good luck!