Calculating how many days passed (between 2 dates)
Hi All,
I am wondering if you can help.
I have a very basic formula in place to calculate how many days passed from date 1 to date 2. I used NETDAYS formula. Since we use it to calculate the time from when the issue was raised until it was resolved - the end date is not always there straightaway which of course brings back #INVALID DATA TYPE error.
Is there a way for the column to stay clear until the end date is provided so the calculation can be done and the correct number can appear in the column? The sheet is to be shared with top management and I'm trying to make it as clear as possible.
I'd appreciate all the suggestions.
Best Answer
-
Hi @angelapaj,
You can do this with an IFERROR statement:
=IFERROR(NETDAYS([Date1]@row, [Date2]@row), "")
If you don't have a value for Date2, then the column will be blank, otherwise the NETDAYS calculation will be done as normal.
Hope this helps, any problems/questions then just post! 🙂
Answers
-
Hi @angelapaj,
You can do this with an IFERROR statement:
=IFERROR(NETDAYS([Date1]@row, [Date2]@row), "")
If you don't have a value for Date2, then the column will be blank, otherwise the NETDAYS calculation will be done as normal.
Hope this helps, any problems/questions then just post! 🙂
-
@Nick Korna it worked! Thank you ! :)
-
what about calculating how many YEARS between dates? I have over 800 contracts that I want to just automatically calculate the contract term.
Or Start Date + 3 years and output End date
-
Hi @WinaHath
For years between dates:
=IFERROR((NETDAYS([Date1]@row, [Date2]@row)/365), "")
This will give you a decimal, but you can add ROUND or similar if you just want the closest amount of years.
For Start Date +3 years I'd use:
=DATE(YEAR([Start Date]@row) + 3, MONTH([Start Date]@row), DAY([Start Date]@row))
In the event of a leap year date being picked (e.g. 29/02/24), the 3 years on date will end up on 1st of March in the third year (e.g. 01/03/27).
Hope this helps!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!