need to have a formula auto fill but only if there are dates in specific cells

I am trying to get the cells to auto fill depending on the dates entered next to it. I can't use the convert to column formula feature because it's not a guarentee that all data in the start date and end date will be entered. We are doing a market review for a certain group of team members and need to let them have the option to enter in any relevant information. If they do not enter any information then I have it set to auto-enter 0 to make the total column formulas to the left work but I can't turn the net day formula into a column formula because having either no data or a 0 will make the rest of the formulas not work. Does that make sense? I don't want to manually add the formulas to these cells but can't think of a way to have it work. Thank you!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Jamie1414

    You can add an IF statement to the beginning of your formula to return 0 if either of your date cells are blank, like so:

    =IF(OR([Employer 2 Start Date]@row = "", [Employer 2 End Date]@row = ""), 0, NETDAYS([Employer 2 Start Date]@row, [Employer 2 End Date]@row))

    This way you actually can set it as a Column Formula so you never need to enter it again. Let me know if this makes sense and works for you!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!