/ How to

How to Add or Subtract Days From Dates in Smartsheet Calendar

Smartsheet is a popular work management and collaboration software that allows users to plan, track, automate, and report on work. One of Smartsheet’s key features is its calendar view, which provides a weekly or monthly perspective on the dates in your sheet or report.

Being able to add or subtract days from dates directly in Smartsheet’s calendar can be extremely useful for planning and scheduling purposes. Here is a step-by-step guide on how to do this.

Prerequisites for Using Calendar View

Before you can start adding or subtracting days in calendar view, you need to make sure:

  • Your sheet has at least one column formatted as a Date column
  • You are currently viewing the sheet in calendar view

To switch to calendar view:

  1. Click on the View drop-down menu on the toolbar
  2. Select Calendar

Adding Days to Dates

To add days to a date:

  1. Double click on the task/event you want to update
  2. This will open the Edit Row form for that item
  3. Click on the date field and update it by adding the number of days required
  4. Click Save

For example, if you have an event on 1/1/2023 and you want to add 5 days to it, you would update the date field to 1/6/2023 and save the changes.

The new date with the additional days will now reflect directly on your calendar view.

Adding the Same Number of Days to Multiple Dates

If you need to add the same number of days to multiple existing dates, you can do this quickly using the fill handle, rather than editing each row individually.

To do this:

  1. Click on the date cell that has the formula to add days already configured
  2. Hover over the bottom right corner of the cell until you see the fill handle (a small + sign)
  3. Click and drag the fill handle down to all the rows/cells where you want that same calculation applied

This will automatically adjust the dates across all selected rows by the specified number of days.

Subtracting Days from Dates

The process for subtracting days from dates is very similar:

  1. Double click the event to open the Edit Row form
  2. Update the date field by subtracting the number of days needed
  3. Click Save

To subtract 3 days from a date, simply enter the date as it would be with 3 days deducted from it.

If you need to subtract the same number of days from multiple dates, again you can use the fill handle to quickly copy the formula down to other rows.

Displaying Date Ranges

Smartsheet also allows you to define date ranges for events spanning multiple days.

To adjust a date range:

  1. In calendar view, click and drag the edge of the date range block to expand or contract the range
  2. You can also double click the event and edit the start/end dates individually before saving

Adjusting date ranges in calendar view allows you to quickly visualize how rescheduling affects other events in your schedule.

Adding or Subtracting Days Using Formulas

Rather than changing dates manually, you can also use formulas to calculate new dates based on adding or subtracting a specified number of days.

Some examples:

=[Start Date] + 5

Adds 5 days to the start date value

=[End Date] - 2

Subtracts 2 days from the end date value

=[Start Date] + [Number of Days]

Adds a dynamic number of days defined in another column

The key things to remember when using formulas with dates:

  • Both the date column and the column containing the formula need to be formatted as Date columns
  • Numbers in formulas are treated as days by default when calculating dates

By leveraging formulas, you can save time from having to manually adjust multiple dates when changes occur. The formulas will automatically recalculate based on the latest values.

Referencing Date Columns in Formulas

When you reference a date column in a formula, make sure to use the @row syntax, for example:

=[Start Date]@row + 5

This ensures you are adding 5 days specifically to the start date value in that row.

You can also reference dates from other rows using row numbers if needed, for example:

=[Start Date]3 + 5

Would add 5 days to the start date value from row #3.

Smartsheet Date Keyboard Shortcuts

Smartsheet offers several keyboard shortcuts for quickly entering dates:

  • t: Inserts today’s date
  • +n: Enters a date n days from today
  • -n: Enters a date n days before today
  • tom: Tomorrow’s date
  • yes: Yesterday’s date

For example, if today is June 15th:

  • +5 would enter June 20th
  • -3 would enter June 12th

These shortcuts can save you time compared to clicking through the date picker or manually entering dates.

Configuring Date Display Format

The format used for displaying dates in Smartsheet automatically adapts based on your personal regional settings.

Common date formats include:

  • MM/DD/YYYY
  • DD/MM/YYYY
  • YYYY/MM/DD

To update the formatting used for dates:

  1. Click on your profile icon > Personal Settings
  2. Click Settings
  3. Under Regional Preferences, select your desired locale from the dropdown menu
  4. The date format will update automatically based on the region selected

Changing your regional preferences allows you to view dates in your preferred format.

Best Practices When Working With Dates

Here are some key best practices around working with dates in Smartsheet:

Use date columns for actual dates – Make sure any column storing date values is configured as a Date column type to avoid issues with calculations.

Reference dates dynamically – Reference date values directly in formulas using @row rather than absolute cell references. This makes your formulas more reusable.

Utilize shortcuts – Use Smartsheet’s date shortcuts like +5 or -3 to quickly enter dates relative to today’s date.

Validate date ranges – Double check date ranges in calendar view to catch any issues with overlapping events or scheduling conflicts.

Automate notifications – Set reminders, update requests or change notifications based on date columns to automatically stay up to date as dates shift.

Following these best practices will help you be more efficient and accurate when managing project schedules using dates in Smartsheet.

Common Date Calculation Errors

When working with dates in Smartsheet, there are a few common errors that may occur:

#Invalid Column Value – This happens when you try to perform date calculations in a column that is not formatted as a Date column. Make sure all date columns and any columns with date formulas are properly formatted.

#Invalid Data Type – Occurs when you reference a column in a formula that does not contain true date values, often caused by text formatted to look like dates. The source columns need to contain valid dates.

#Circular References – Creating formulas that reference their own column can cause circular reference errors. Structure your formulas to reference dates from secondary columns.

Paying attention to these errors will allow you to troubleshoot any issues that arise from date calculations.

Additional Date Functions

In addition to adding/subtracting days from dates, Smartsheet also offers several functions for more advanced date calculations:

DATE() – Creates a date value from a year, month and day specified individually

TODAY() – Returns the current date

WEEKDAY() – Returns a number representing the day of week of a date (1 = Sunday, 7 = Saturday)

DAYS() – Calculates the number of days between two supplied dates

YEARS() – Calculates the number of full years between two dates

See Smartsheet’s date documentation for more details on utilizing these handy date functions.

Summary

Being able to accurately add, subtract, and calculate with dates directly in Smartsheet Calendar view is crucial for planning and scheduling projects effectively.

By following this guide you should now understand:

  • How to add or subtract a fixed number of days to individual or multiple dates
  • Techniques for adjusting date ranges for multi-day events
  • How to leverage formulas to calculate dates based on adding/subtracting days
  • Useful keyboard shortcuts, display formatting, and functions for working with dates
  • Common errors to watch out for and how to avoid them

Mastering date calculations in Smartsheet will enable you to keep complex project timelines on track and gain deeper insights into how adjusting dates affects downstream tasks and events.

Related Posts