Buzz
This article provides a detailed guide on leveraging functions in Excel 2013 and 2016 to identify and compute date-related information.
To effectively utilize functions related to date information, let's explore the following aspects:
1. The unique relationship between Excel and dates.
1.1 How are dates related to Excel?
- Unlike numerical or text data, Excel stores dates uniquely and differently from other data types. Dates in Excel are stored in text format as serial numbers recognized by Excel as dates. For example, the date 01/01/08 -> the serial number stored in Excel is 39448.
- Excel's calendar starts from the year 1900 – the inception of Excel. The conversion of dates to Excel's serial numbers is calculated as follows: Excel considers 01/01/1900 as 1, and each subsequent day adds 1 to the serial number. Specifically:
1.2 Excel-supported date formats.
- To discover the date formats supported by Excel, follow these steps:
+ Right-click on any cell in Excel -> Format Cells:
+ A dialog box appears in the Number tab -> Date -> supported Excel formats are in the Type: section.
+ Additionally, Excel supports users defining date types based on Excel suggestions. Click on Custom -> supported Excel formats are in the Type: section.
- Besides the mentioned formats, Excel won't recognize it as a date and will either consider it as a text string or display an error message.
2. Utilizing formulas in date calculations.
Some commonly used functions in date calculations:
2.1 Calculate the number of days between 2 specific moments.
- To calculate the number of days between two specific dates, subtract the start date from the end date, ensuring the dates adhere to the Excel format.
For example:
- In cases where the end date is earlier than the start date -> the returned value will be less than 0.
2.2 Calculate the working days between two specific moments (excluding holidays and weekends).
- To calculate the working days, use the NETWORKDAYS() function. For example, calculate the working days when the start date and end date are known.
- In cases where the end date is earlier than the start date -> the function returns a number of days less than 0:
2.3 Find a date after a certain number of working days.
- To find a date after a specific number of working days, use the Workday function. For example, find the completion date of a project when the number of days to complete the task is 120 days, and the start date is 10/10/2016, with 2 holidays during that time on 10/12/2016 and 10/24/2017.
2.4 Find a moment after 1 month.
To find a date after 1 month -> use the Date() function. For example, find the date after 3 months from 10/10/2016:
2.5 Find a moment after a specific number of years, months, and days.
- To find a date after a specific number of years, months, and days -> use the Date() function. For example, find the date after 3 years, 9 months, and 27 days from 10/10/2016:
This is a detailed guide and specific examples for determining information and performing date calculations in Excel 2013.
Wishing you success!
