Date Calculation not working correctly

egardner

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

Paul Newcome

Make sure all columns containing dates are set as date type columns.

egardner

Hi @Paul Newcome ,

All columns containing dates are set as date type columns.

Thanks,

Emma

Paul Newcome

Ok. And what formula(s) do you have populating the source dates?

egardner

Hi @Paul Newcome

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

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