Subtracting Days from a Date
Hello,
I am trying to subtract 30 days from a column pulling in dates from another sheet using formulas and it keeps yielding an #INVALID OPERATION error. I have already checked and do not have dependencies enabled. Both columns are formatted as date columns. While playing around I noticed when I add the days it instead tacks the number to the end of the year. How do I correct this?
Formula/function needed but causing error: =[Target Activation Date]@row - 30
Formula discovered just tacking number onto the end of the year instead of adding days: =[Target Activation Date]@row +30 (Picture for reference)
30
Best Answer
-
The current formula will pull date values if the source data is date values.
If one of the columns is not showing the calendar (when no formula is applied), it is not set as a date type column.
How is the source data being populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Hello,
Have you ensured the Column Type is a Date?
This option is visible by hovering over the column and clicking the 3 dots.
-
Yes I have already verified the column type for both columns is date.
-
What are the formulas populating the date columns?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I suspect that the date is entered as text - example in Date2 Column, the first date is text (in a date column) and the second is a date.
Another clue that it is not a date: when clicking on the calendar icon in the text date cell, it does not go to the date shown, it shows today's date:
Formulas to convert the text to date will depend on how consistent the data is. Do all the dates use 2 digits for month, date and year (01/01/01 vs 1/1/01).
-
The formula for the Target Activation Column is =IFERROR(INDEX({Automations Target Activation Date}, MATCH(MCC@row, {Automations MCC #}, 0)), "")
I did notice that while I restricted both columns to date only, the calendar option does not display on the target activation column. The data is always going to be in the mm/dd/yy format. Thank you for the tip on using a formula to convert text to date. Is there a way to modify my existing formula to ensure it is read as a date?
-
The current formula will pull date values if the source data is date values.
If one of the columns is not showing the calendar (when no formula is applied), it is not set as a date type column.
How is the source data being populated?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This was the solution thank you! The original source from the other sheet was not set to date, it was set as text. I adjusted and the error has disappeared and the correct date is populating instead. Thanks again!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!