Smartsheet Formula Issues

Jason Whittington

I am trying to import a rolling completion log and I am having issues getting an aging formula to work. The formula looks at a date noted column and calculates total aging until a date is placed in a date resolved column. It also returns an empty cell if there is not a date in the date noted column.

 

Any help would be greatly appreciated.

Joel Johnson

Hello Jason,

 

Try a formula like this:

 

=IF(ISBLANK([Date Noted]1), " ", NETDAYS([Date Noted]1, [Date Resolved]1) + " Days")

 

It basically checks for a blank in the date first, and then does the calculation.  There are several different date functions in smartsheet, which can calculate days based on non working days as well.  This is just a simple days between calculation.

 

Good Luck.

 

Jason Whittington

That formula works, but if the date resolved column is empty, I get an error. I have tried quite a few other formulas, but each of them errors out when I do not have a value in the Date Resolved column....

Jason Whittington

My original formula works great in Excel, but I am still trying to learn which formulas Smartsheet likes.

Travis

Joels formula is only checking for one blank cell but you have two possible blank cells which could cause an error if the second cell is blank. Try this which will check both fields for a date before continuing on with the formula:

 

=IF(AND(ISDATE(Date Noted]1), ISDATE([Date Resolved]1)), NETDAYS([Date Noted]1, [Date Resolved]1) + " Days")

 

 

Jason Whittington

Cool, thanks. I'll give it a shot.