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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!