calculate length between the Date of Event column and two other columns

OshaK

I need to calculate the interval in days (and the average) between the 'Date of Event' column which gets submitted by a user in the form and the Dates the submission was made.

1. Column "Recorded Date (original from Qualtrics)" - data was entered manually for the retroactive data (since we rolled it over from Qualtrics) in one column.
2. Going further, we would like to calculate the same interval in days between the 'Date of the Event' and the 'Date Created' column which is automatic from SS (these dates are all the same since we rolled data on a single day.
3. Or, is there a way to manually edit the 'Date Created' column and merge 'Recorded Date' into it?
4. If we calculate the interval between the 'Date of the Event' and those two columns, is it possible to chart it on a single chart and calculate the common average?

Julie Fortney

Are your Recorded Date... and Master Date columns both set up as date columns? The invalid column value error means that one or more of your columns is not in the format needed to perform the calculation.

This help article is great to keep on hand when you get formula errors: https://help.smartsheet.com/articles/2476176-formula-error-messages

Julie Fortney

Hi @OshaK,

You could add a master date column with this formula:

=IF([Date Created]@row = DATE(2023, 2, 3), [Recorded Date (original from Qualtrics)]@row, [Date Created]@row)

This is telling Smartsheet that if the Date Created is 2/3/23 (which appears to be the date you imported your data), you want it to pull the date from the original Qualtrics date column. If the Date Created is another date, pull that date.

You could then calculate the days interval between your master date column and the Date of Event.

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

OshaK

thank you @Julie Fortney for some reason, I'm getting an invalid column value error. I just copied/paste the formula from this thread. Thank you.

Julie Fortney

Are your Recorded Date... and Master Date columns both set up as date columns? The invalid column value error means that one or more of your columns is not in the format needed to perform the calculation.

This help article is great to keep on hand when you get formula errors: https://help.smartsheet.com/articles/2476176-formula-error-messages

OshaK

oh so sorry, @Julie Fortney , the master date wasn't, I switched and it's working, thank you!

Julie Fortney

You're very welcome; I'm glad to hear it's working!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt