When dealing with dates like deadlines, you will more often find the need to extend them by a few days. You can simply use the plus operator or SUM function to add days to date.
To add days by months or years as well, use the DATE function. Similarly, if you want to add only working days excluding holidays, use the WORKDAY function.
Use Plus Operator to Add Days
The plus operation (+) sums up two or more values. As dates are assigned a numeric serial key, Sheets can perform arithmetic operations like addition on date values.
Let’s use the plus operator to add days to the dates.
Use SUM Function
The SUM function is a dedicated Google Sheets function to add values. You may also use the SUM function instead of the plus operator to add days to dates in Google Sheets.
Syntax:
=SUM(value1,[value2],...)Let’s add up the same dates as before using the SUM operator.
Add Years, Months, and Days to a Date with DATE Function
Use the DATE function to add days if you’ve separated your dates by year, month, and day. The DATE function will not only combine your dates, but also allow you to add values by year, month, and day.
Syntax:
=DATE(year,month,day)Take a look at this spreadsheet:
We’ve separated the dates by year, month, and day in columns A, B, and C, respectively. In column D, we’ve entered the number of days we want to add to the date.
Here’s the formula we entered in Column E:
=DATE(A2,B2,C2+D2)
If I had to increase the date by months or years, I would simply add more values in the year and month section.
In the same data set, let’s increase the dates by 1 year and 7 months:
=DATE(A2+1,B2+7,C2)
Add Only Working Days to Date
Syntax:
=WORKDAY(start_date, num_days, [holidays])The WORKDAY function comes in pretty handy when managing business deadlines. When adding days to dates, WORKDAY excludes Sundays and Saturdays as they’re usually off days. Additionally, you can add more dates you wish to exclude when adding days.
In the following data table, I have 10 date values in column A and the extension in column B. In range E5:F7, I have a data table containing holidays.
I want to calculate the new deadline, excluding off days. The off-days include the weekends, Christmas, and New Year’s Day.
In cell D2, here’s the formula I entered to generate the new deadline:
=WORKDAY(A2,B2,$F$6:$F$7)
Add Days Based on a Condition
Say, you only want to extend deadlines for employees who attended a conference. To set such conditions, use the IF function.
Syntax:
=SUMIF(logical_expression, value_if_true, [value_if_false])In column B, I have the original deadlines. Employees who attended the conference have been marked present in column C.
Let’s extend the deadline by two days for those employees who attended the conference:
=IF(D2="Present",B2+2,B2)
Add Days to the Current Date
Say, you’re creating a to-do list and want to set the deadline for the next 4 days. You can use the TODAY function to generate today’s date, then add days using the plus operator.
Here’s the to-do list I’ve created for the week:
In column C4:C13, I’ve set the days required for each task. To create a deadline, here’s the formula I entered and then applied to the entire column:
=TODAY()+C4
I have also covered a guide to add days to dates in Excel. Don’t forget to check it out!