Need Help Calculating Work Day Differance Between 2 dates.

Hello All

So I have 2 column I am trying to calculate the work day differance.

Pch Final Insp live sc and Pch Final Insp baseline 115.

Problem I am having. If the dates are the same it shows a 1. I want it to show a 0. if the days are one day ahead or one day behind it shows a -2 or 2. It should show a -1 or 1.

Here is the formula I am using.

=NETWORKDAYS([PCH Final Insp Live Sc]@row, [PCH Final Insp Baseline 115]@row)

How do I get it to calculate correctly the way I want it to?

Thanks

Screenshot 2024-07-24 213315.png

Find more posts tagged with

Accepted answers

All comments

Sing C

Hi @Jon Barto, it's a formula interpretation thing.

The way I read the definition of the formula is "the number of working days between start date and end date" so if the same dates are entered, I'm asking for the number of working days between today and today, which is 1.

It's similar logic to our internal vacation booking system. If I want to take 1 day of vacation, I will enter the same date in the start and end field of the request form because I am taking that day off.

I get what you're trying to do and the way I've solved for it is include -1 to the end of the formula, so you get:

=NETWORKDAYS([PCH Final Insp Live Sc]@row, [PCH Final Insp Baseline 115]@row)-1

Let me know if that approach gives you what you need.

Thanks and have a great day!

Jon Barto

@Sing C

Unfortunally That only works when the days are the same. If they are one day different the -1 really messes up the day differance. say if first date is 12/4/24 and second date is 12/3/24 it returns a -3… It should return a -1 Is there a way to incorperate an if statement about one date greater than the other?

Sing C

@Jon Barto Are there any rules around how the two dates compare? For example, will one always be the same date or later than the other. Based on a reading of the column names, baseline suggests the earlier of the two dates (or at least the same as) with final being the same date or later than the baseline.

If the one date could be earlier, the same, or later than the other date, then you would need to use an IF statement to either apply -1 or +1 depending on which of the two dates was later.

IF date 1 > date 2, add 1 to the end of the networkdays formula. So in your example of 12/4 first date and 12/3 second date - which would give a result of -1.

IF date 2 >= date 1, minus 1 to the end of the networkdays formula. So in an example where 12/3 is the first date and 12/4 is the second date - which would give a result of 1. The >= part would also cater for the situation where the 2 dates are the same, and give you a result of 0.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.