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
    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!



    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!