Jump to content

Counting days in between multiple dates


This topic is 606 days old. Please don't post here. Open a new topic instead.

Recommended Posts

So I have multiple dates sort of like a start to end dates.

Date1, Date2, Date3, Date4, Date5

Each date is later than the previous date.

My question is how can I count the days in between those dates (excluding the weekends and holidays)? For example:

Date1 to Date2 = 3days

Date2 to Date3 = 7days

Date3 to Date4 = 2days

Date4 to Date5 =12days

Total Days = 24days

I followed the guide on this page https://support.claris.com/s/article/Calculating-Number-of-Weekdays-Work-Days-Between-Dates-1503692920764?language=en_US and it works fine without the holidays. But I am having difficulties when trying to include the holidays in the calculation since the example only has two dates which is the StartDate and EndDate and in my case I have multiple dates and some dates in my case can be a starting date and also can be an end date like Date2, Date3 and Date4.

How can go about doing this?

Link to comment
Share on other sites

I think there are basically two ways to account for holidays when calculating the difference between 2 dates: 

1. Define a relationship to the Holidays table that includes all holidays between your start date and end date. Then simply count the number of related records and subtract it from the calculated difference. However, this method requires holidays that fall on a weekend to be either marked as such or excluded from the Holidays table altogether.  

Since you want to calculate 4 different intervals, this method would not suit you well because you would need to define a separate relationship to the Holidays table for each one of the 4 intervals.

2. Use a looping method to go over each day between your start date and end date and add it to a count only if it is not a weekend or a holiday.

To avoid repeating the same code 4 times, you might want to define a custom function for this. The function could look like:

WorkDaysInRange ( startDate ; endDate ; listOfHolidays ) =

While ( [
d = startDate ;
n = 0 
] ;
d ≤ endDate  ;
[
weekend = DayOfWeek ( d ) = 1 or DayOfWeek ( d ) = 7 ; 
holiday = not IsEmpty ( FilterValues ( d ; listOfHolidays ) ) ;
n = n + not ( weekend or holiday ) ; 
d = d + 1 
] ;
n
)

Important:
The function performs the comparison between the date and the list of holidays as text. That means that dates in both tables must be entered in the exact format used by your file; a date calculated as "01/08/2022" will not be matched against a holiday entered as "1/8/2022". If necessary, define a calculation field in the Holidays table that converts each holiday date to a number, then compare GetAsNumber ( d ) against a list of these numbers.

Also, depending on which holidays you observe, you could possibly do without a pre-generated list of holidays and calculate them on-the-fly.

 

4 hours ago, emtau said:

That page his hopelessly outdated - and it wasn't a very good method to begin with.

 

  • Like 1
Link to comment
Share on other sites

One more thought:

It's not clear whether you want to include both start date and end date in the count. If you do (as my function above does), then you cannot simply add up the 4 intervals to get the total, because it might include the boundary dates twice.

 

Link to comment
Share on other sites

Thank you for your reply,

and yes

17 hours ago, comment said:

To avoid repeating the same code 4 times, you might want to define a custom function for this. The function could look like:

I figured that would happen...

I'm no expert but the solution you gave me using a loop is a bit sketchy to my level of understanding. If you could be so kind and take a look at the sample file I've uploaded since that is exactly how I want it to look. The exclution of weekends seems to work fine but the exclution of Holidays is a problem for me. I've created both tables but no relationship between them yet since I'm not sure what to do. Please add/remove/edit whichever way you like to make it work and I could then try and understand your solution.

Please take a look at my simple sample file and make it work to also exclude the holidays.

Much appreciated.

countdays.fmp12

Link to comment
Share on other sites

I have installed the custom function above into your file, with one modification: I changed the exit condition to d < endDate  in order to exclude the end date from the count.

I related the 2 tables using the x operator, making all records in one table related to all records in the other table. Ideally, only holidays in the given range need to be related - but if I understand your use case correctly*, some records might not have a value in the Task5 field, so that would be a bit more complicated.

I added 2 calculation fields for the first 2 intervals. Check the results in record #2 which actually spans some holidays.

---
(*) I think your structure is problematic (numbered fields are always a warning sign). I believe you should have a child table of Tasks, where each task would be an individual record. Then each task can calculate its own duration independently.

 

countdays+.fmp12

  • Like 1
Link to comment
Share on other sites

On 7/29/2022 at 5:00 PM, comment said:

I added 2 calculation fields for the first 2 intervals. Check the results in record #2 which actually spans some holidays.

Thank you for your solution... your custome function "WorkDaysInRange" is a life changer.

 

On 7/29/2022 at 5:00 PM, comment said:

(*) I think your structure is problematic (numbered fields are always a warning sign). I believe you should have a child table of Tasks, where each task would be an individual record. Then each task can calculate its own duration independently.

I didn't quite understand what you mean "numbered fields" you meant in the Holiday table?

Each Task as its own record; well there really not much to add in each task beside just a date so I'm not seeing the reason to create different tables for different tasks or I'm not quite getting the whole picture of what u meant.

 

By the way thank you very much I will try your solution and consideration.

Edited by emtau
Link to comment
Share on other sites

2 hours ago, emtau said:

I didn't quite understand what you mean "numbered fields"

I meant the fields named Task1, Task2, Task3, Task4 and Task5. 

 

2 hours ago, emtau said:

I'm not seeing the reason to create different tables for different tasks

I did not suggest "different tables for different tasks". I do suspect you should have a separate record for each task in a related table - but I don't really know what these "tasks" represent in real life and what do you intend to accomplish by tracking them. Still, it is very rare to see a case where numbered fields are not a symptom of poor structure. Even a repeating field with 5 repetitions would be a better solution than that.

 

Link to comment
Share on other sites

This topic is 606 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.