How to Calculate Dates Between Columns

Beronica Muller

Hello,

I am attempting to calculate days between two column dates

network days1.png

network days2.png

within Smartsheet. I used the NETWORKDAYS({column1},{column2}) formula and received the following error message #UNPARSEABLE

Screenshots have been attached for your review.

Paul Newcome

They syntax looks correct assuming the formula is on the same sheet as the data. Double check your column names for spelling.

I also notice that your third cell reference is different from the other 3. The hyphen is shorter. I am not sure if that is in the formula, or if it is just in typing it here in the Community, or if it is even an issue at all. I just noticed that it does appear different. Maybe start there and see if correcting that can clear things up.

Paul Newcome

Are the columns on the same sheet as the formula?

Beronica Muller

No, they are not. I think that I have figured it out. I added a column to the smartsheet and used the following formula: =NETWORKDAY([Due Date]1, [Submitted Date]1) * -1. Is that correct?

Paul Newcome

You would need something on the source sheet to calculate the difference on each row. What you have should work for that.

Then further calculations such as averaging or summing could be done on another sheet.

Beronica Muller

Okay thank you. One more thing, I am trying to calculate the fields that are blank. I am getting an error message #UNPAESEABLE. I used the following formula:

=IF(ISBLANK([Days (Due Date – Submit Date)]@row), "", IF([Days (Due Date – Submit Date)]@row <= 5, "Green", IF(AND([Days (Due Date - Submit Date)]@row > 5, [Days (Due Date – Submit Date)]@row >= 30), "Amber", "Red")))

What am I doing wrong?

Paul Newcome

They syntax looks correct assuming the formula is on the same sheet as the data. Double check your column names for spelling.

I also notice that your third cell reference is different from the other 3. The hyphen is shorter. I am not sure if that is in the formula, or if it is just in typing it here in the Community, or if it is even an issue at all. I just noticed that it does appear different. Maybe start there and see if correcting that can clear things up.