Combining 2 formula's to one for count of days between date ranges
Hi!
I am trying to calculate to get a the number of days between two date columns.
I have 2 date columns.
Date Reported
Date Resolved
There will ALWAYS be a date in "Date Reported", however, in "Date Resolved" the date could be blank (so I would want to count is as "current day".)
I have the formula to get the number of "days" between Date Reported and Date Resolved, when a date is populated in both fields, working.
I have the formula to get the number of "days" between Date Reported and Date Resolved, when a date is populated in Date Report, and Date Resolved is blank, working.
I am trying to combine these two formula's to be provide the correct # of days for either scenario above. I have tried "or" and "if count" (and a few others...)by reading posts here, however I get either parsing errors or invalid data type errors.
Here are my two formula's that I want to turn into one formula that I can use for column "Business Days Open"
Formula 1:
=NETWORKDAY([Date Reported]@row, [Date Resolved]@row) - returns # of days between 2 dates.
Formula 2:
=IF([Date Resolved]@row = "", TODAY() - [Date Reported]@row) - returns # of days between Date Reported and current date.
Any assistance would be greatly appreciated.
Thank you,
Jen
Best Answer
-
You are going to want to nest an IF statement within the NETWROKDAY function.
=NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY()))
Answers
-
You are going to want to nest an IF statement within the NETWROKDAY function.
=NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY()))
-
Thank you!!! I tried the nested "if" statement, but obviously was not formatting it correctly. Thank you so much for you assistance!
-
Happy to help. 👍️
-
Hello... using this formula, =NETWORKDAY([Date Reported]@row, IF([Date Resolved]@row <> "", [Date Resolved]@row, TODAY())), how would the formula be modified to ignore and not return invalid data type error, if "date reported" is blank?
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!