netdays if formula returning #invalidoperation


Amateur formula user here!

I've tried rearranging this formula few different ways, and I believe my issue is just the order of events?

I'm looking to calculate length of stay by using =netdays with the "Enrollment Date" as the start and the "Discharge Date" as the end. However, if discharge date is blank, then the end date shall utilize today's date.

Here's the formula that I'm using that return #invalidoperation:

=NETDAYS([Enrollment Date]@row, IF(ISBLANK([Discharge Date]@row) = TODAY(), [Discharge Date]@row))

What's wrong with my formula? I also tried this alternative that produced #unparseable:

=NETDAYS([Enrollment Date]@row, [Discharge Date]@row, IF([Discharge Date]@row ISBLANK(TODAY()))

Any help is much appreciated!




  • Matthew Flebbe
    Matthew Flebbe ✭✭✭✭

    Hi @kristina.kazakoff,

    It looks like you can get away with a standard IF statement and forego the ISBLANK statement. The double quotes in the formula below will work just as well to determine a blank cell.

    Try this:

    =IF([Discharge Date]@row = "", NETDAYS([Enrollment Date]@row, TODAY()), NETDAYS([Enrollment Date]@row, [Discharge Date]@row))

    Hope that helps.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!