Create formula to calculate network days in blank cells

Options
Beronica Muller
Beronica Muller ✭✭✭✭
edited 04/23/21 in Formulas and Functions

I need to create a formula that will track aging up to and including the day files are processed by an analyst. Once the “Date file received” column is populated with a date, the formula should count the weekday(s) up to and including “Date Processed” date.

For example, if the date file received date is 4/22 and the date processed date is 4/22 the file age column should have a 0.

There will be some blank cells in both columns as there will be files not received or processed. How do I create a formula that will generate and count the network days of cells that already have dates entered and once a date is entered in the blank cell? Here is what I have thus far as I am not sure how to add in the blank cell formula.

=IF([Date File Recd]@row = [Date Processed]@row, "0", IF(NETWORKDAYS([Date File Recd]@row, [Date Processed]@row))

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Beronica Muller

    What about adding a statement at the beginning that says if either of these dates are blank, return blank...otherwise continue with the rest of your formula. Like so:


    =IF(OR([Date File Recd]@row = "", [Date Processed]@row = ""), "", IF([Date File Recd]@row = [Date Processed]@row, 0, NETWORKDAYS([Date File Recd]@row, [Date Processed]@row)))


    Note: I took out the "quotes" from your 0 to make sure it returns a numeric value. I also removed out the IF around your NETWORKDAYS formula, as it doesn't need to be there. This is the final instruction on what to do should either of the previous IF statements be false. Does that make sense?

    Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Beronica Muller

    What about adding a statement at the beginning that says if either of these dates are blank, return blank...otherwise continue with the rest of your formula. Like so:


    =IF(OR([Date File Recd]@row = "", [Date Processed]@row = ""), "", IF([Date File Recd]@row = [Date Processed]@row, 0, NETWORKDAYS([Date File Recd]@row, [Date Processed]@row)))


    Note: I took out the "quotes" from your 0 to make sure it returns a numeric value. I also removed out the IF around your NETWORKDAYS formula, as it doesn't need to be there. This is the final instruction on what to do should either of the previous IF statements be false. Does that make sense?

    Let me know if this works for you!

    Cheers,

    Genevieve

  • Beronica Muller
    Beronica Muller ✭✭✭✭
    Options

    @Genevieve P It makes sense now. Thanks for taking out the time and explaining this to me! ☺️

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem, I'm glad I could help! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!