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:
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:
+ The dialog box appears in the Number tab -> Date -> formats that Excel supports are in the Type:
+ 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:
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:
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:
- Where the end date is less than the start date -> the return value is less than 0.
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.
- In case the end date is less than the start date -> the function returns the number of days less than 0:
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.
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:
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:
The above are specific instructions and examples for defining information and calculating dates in Excel 2013.
Good luck!