Date Calculation not working correctly
In my Operating Calendar sheet, I have a Due column (date). This due date column should be calculating based on a formula that adds the "Days From Pricing Group OR 1st Departure Date" column to the "Pricing Group Date" Column (=[Pricing Group Date]@row + [Days From Pricing Group OR 1st Departure Date]@row). Both the "Days Out" and "Pricing Date" fields are populated using and INDEX(MATCH()) formula to the "Task List" sheet. All columns that contain dates are configured to be "date" columns (as opposed to text.)
In theory, the date should adjust by the number of days in the Days Out column as a result. It is not working as intended and is currently showing the date and then the text of what is in the "Days Out" column (see below.)
I've tried using =[Pricing Group Date]@row + VALUE([Days From Pricing Group OR 1st Departure Date]@row) to see if it makes any difference and that didn't solve it either. I'm wondering if it has something to do with the fact that the cells are linked / formulas rather than hard data. That being said, I tried recreating with another sheet and didn't get the same problem.
Does anyone know why this might be happening? Any help would be much appreciated.
Thanks,
Emma
Answers
-
Make sure all columns containing dates are set as date type 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!
-
-
Ok. And what formula(s) do you have populating the source dates?
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!
-
Do you mean the "Pricing Group Date" column? That column in the Operating Calendar is using an INDEX(MATCH()) formula to pull the "Pricing Group Date" from the "Pricing Groups" sheet (hard data in a date-formatted column.)
Let me know if that answers your question!
Thanks,
Emma
-
What is that exact formula, and how is the date populated in the other sheet (and did you check to make sure that's also a date type column)?
What we need to do is trace the data all the way back to the source and figure out if that is a date.
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!
-
Hi @Paul Newcome -
Smartsheet was able to get this figured out for me. For whatever reason, the negative numbers auto-formatted to populate an apostrophe before them, rendering them as text as opposed to a number. I was able to go through and remove them and now everything is working.
Thanks so much for your help!
Emma
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!