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].

Find more posts tagged with

Accepted answers

All comments

StephanieStepney

@StephanieStepney tagging myself so when you get an answer I'll also find out! :)

Paul Newcome

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

@Latreva Washington following

Genevieve P.

Hey @Latreva Washington

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.