Navigating date mathematics in Excel? You're not alone. Calculating date days in Excel, albeit not entirely intuitive, is a task that comes up frequently. Let's demystify this process, so you can count days like a pro.
The first step is understanding that Excel stores dates as serial numbers. This numerical representation makes calculating date intervals straightforward.
Calculating Date Difference in Days
Imagine you have two dates, DFT (Date From) and DTO (Date To), and you want to find the number of days between them. Here's how:
Syntax: DTO - DFT
Example: Assuming DT represents your date in Excel, simply subtract one date from the other: DT2 - DT1.
Challenges with quarters and years
Things get trickier when dealing with quarters or years. For instance, Excel considers '01-01-2020' as Year 1900. This leads to erroneous calculations. Here's how to handle this:
Solution: Convert your dates to serial numbers.
Using DATE function
Excel's DATE function simplifies this, converting individual day, month, and year values into Excel's date format.
Syntax: DATE(year, month, day)
Example: date from year 2020, month 4, and day 1, would be entered as =DATE(2020, 4, 1).
Counting Business Days (Excluding Weekends)
Need to exclude weekends from your calculations? Here's how to only count business days (Monday to Friday):
Solution: Utilize the NETWORKDAYS function, which returns the number of whole business days between two dates.
Excluding holidays
What if you also need to exclude holidays? Use the NETWORKDAYS.INTL function. It lets you specify the holidays, making it highly versatile.
Syntax: NETWORKDAYS.INTL(start_date, end_date [, holidays])
Example: NETWORKDAYS.INTL(DT1, DT2, {"01/01/2020", "07/04/2020", "25/12/2020"})