As we have said in our previous tutorial on finding the difference between two dates, Google Sheets stores the dates in a special format which allows one to perform addition and subtraction.
To find a certain date after the start date, simply add the number of days through simple addition. Note that the count begins the next day after the specified start date.
Similarly, if you’d like to find the date a certain number of days before the given date, you can simply do so through subtraction. Subtraction also skips the given date in the calculations.
Shown below are examples:
If you need to look for a certain date a given number of months after the given date, you can use the EDATE function. The EDATE function gives you the date a certain number of months after or before the given date. The syntax of the function is as follows:
=EDATE(start_date, months)
Both the start_date and months can either include the actual dates or point to the cell containing the given values. The output is given in an MM/DD/YYYY format.
You can check the examples below:
Google Sheets also allows you to instead count just the workdays, not including the weekend and the holidays. You can use the WORKDAY and WORKDAY.INTL functions for this.
The WORKDAY function automatically ignores the weekends (Saturday and Sunday) when counting the dates. The syntax is given as follows:
=WORKDAY(start_date, num_days, [holidays])
The WORKDAY function allows you to specify the start date, the number of days, and also the holidays that fall on the workdays covered by the number of days. You can specify the cells containing the start date, the number of days, and the holidays. The examples are shown below:
The WORKDAY.INTL function gives you more versatility by allowing you to specify the days that comprise the weekend. The syntax of the function is given below:
=WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
The weekends can be specified using two different methods: the string method and the number method. By default, the weekend is set to 1, specifying Saturday and Sunday as the weekend days. The string method is a simpler method since it visualizes the days of the week into a string of 0’s and 1’s. Here’s a few examples:
0000011 - weekend at Saturday and Sunday
1000000 - weekend at Monday
1010100 - day-offs at Monday, Wednesday, and Friday
Make sure the weekend format is enclosed in quotation marks. You can check the examples below:
You can check the sample sheet below to see these samples work in Google Sheets:
