Count Number of Days Between Two Dates
Hi there. I'm trying to count the number of days between two dates, which I was able to figure out. with this formula:
=IF(ISDATE($[Q1 End Date]$1), IF(NETDAYS([Target Service Start Date]@row, $[Q1 End Date]$1) = 0, 1, NETDAYS([Target Service Start Date]@row, $[Q1 End Date]$1)))
Although, this calculation gives me negatives if the [Target Service Start Date] is after [Q1 End Date]. I need it to give me a 0 if it is After [Q1 End Date].
Basically, I only want it to give me the number of days within each quarter. So if [Target Service Start Date] is before Q1 then it would calculate the number of days between [Q1 Start Date] & [Q1 End Date]. If [Target Service Start Date] is within those two dates then it calculates the number of days between [Target Service Start Date] & [Q1 End Date].
Answers
-
@StephanieStepney tagging myself so when you get an answer I'll also find out! :)
-
Give this a try:
=IF(ISDATE($[Q1 End Date]$1), MAX(IF(NETDAYS([Target Service Start Date]@row, $[Q1 End Date]$1) = 0, 1, NETDAYS([Target Service Start Date]@row, $[Q1 End Date]$1)), 0))
Basically we compare the NETDAYS output to zero and pull the MAX. If the NETDAYS is greater than zero, then that's what will be output. If it is less than zero then zero would be output.
-
@Latreva Washington following
-
Do you have a question? This post is from 2022 - I assume that Paul's formula resolved the question.
If you have a new question, please feel free to post in the Formulas & Functions topic!
Cheers,
Genevieve
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
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!