Create formula to calculate network days in blank cells
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
-
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
-
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
-
@Genevieve P It makes sense now. Thanks for taking out the time and explaining this to me! ☺️
-
No problem, I'm glad I could help! 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!