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
-
Paul Newcome ✭✭✭✭✭✭Make sure all columns containing dates are set as date type columns.
-
egardner ✭✭✭ -
Paul Newcome ✭✭✭✭✭✭
Ok. And what formula(s) do you have populating the source dates?
-
egardner ✭✭✭
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
-
Paul Newcome ✭✭✭✭✭✭
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.
-
egardner ✭✭✭
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
![\"example.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/LCYBMPOFK1T6\/example.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nOnce you remove that, you should be set!<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":110956,"type":"question","name":"Formula IFS return values from other columns","excerpt":"Hi, i am trying to set a new column that should return EAS or ETS text depending of what it is in column named \"ETS\", but i want to add another if that return a value from column \"Business Dropdown\" if the column ETS is blank. This is the formula that i wrote now very simple and it is running well, but it is missing the…","snippet":"Hi, i am trying to set a new column that should return EAS or ETS text depending of what it is in column named \"ETS\", but i want to add another if that return a value from column…","categoryID":322,"dateInserted":"2023-09-28T20:10:28+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T20:35:00+00:00","insertUserID":140104,"insertUser":{"userID":140104,"name":"Raul Cabrera","url":"https:\/\/community.smartsheet.com\/profile\/Raul%20Cabrera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WxHVX93-X34!CelZAvGm7jw!7qwcGhKSeo9","dateLastActive":"2023-09-28T20:50:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":165153,"lastUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T20:51:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":20,"score":null,"hot":3391865728,"url":"https:\/\/community.smartsheet.com\/discussion\/110956\/formula-ifs-return-values-from-other-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110956\/formula-ifs-return-values-from-other-columns","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":110956,"commentID":397531,"name":"Re: Formula IFS return values from other columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397531#Comment_397531","dateInserted":"2023-09-28T20:35:00+00:00","insertUserID":165153,"insertUser":{"userID":165153,"name":"DKazatsky2","url":"https:\/\/community.smartsheet.com\/profile\/DKazatsky2","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T20:51:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/ASKPITW4DU25\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-28T20:50:48+00:00","dateAnswered":"2023-09-28T20:35:00+00:00","acceptedAnswers":[{"commentID":397531,"body":"
