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.
-
-
Ok. And what formula(s) do you have populating the source dates?
-
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.
-
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
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!