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

Jamie1414

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!

Genevieve P.

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!

Cheers,

Genevieve

Genevieve P.

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!

Cheers,

Genevieve

Jamie1414

You're amazing! Thank you so much!

Genevieve P.

No problem! I'm glad I could help 🙂