# Period time with weekends

Hello Knimers : D, hope you´re doing great.

So I was wondering if there´s a way to get time diferrence beetween columns without considering sunday.

For example:

In the last input there’s 2 difference days because it does not count Sunday.

Is there a way, workflow, or node that can help me with this?

BR
-N

Hello @ttsk8der ,

I came up with a solution that uses many nodes with loops. Perhaps not the best solution.

workday cal.knwf (29.8 KB)

3 Likes

Hey @tomljh and @ttsk8der,

maybe this solution, there are actually three for cross verification, are of interest for you. Approach used:

1. Pure mathematical: Given that each week has seven days you can subtract these from the total amount of days in between dates. The edge case for less than seven days but where a Sunday is still included (Start: Saturday; End: Monday) must be factored in.
2. Loop wise approach: Loop over each row and loop per days diff between dates to shift the date based on the iteration. Determine the day of the week for the result. Then do a simple filtering of all Sundays and count via GroupBy the remaining days.
3. Similar to #2 but Count occurrence of days per day of week for each start and end date. Then sum Mon-Sat.

I used Chat GPT to calculate some random dates and their respective count of days w/o Sundays.

Date 1 Date 2 Days Total expected (Chat GPT) Days without Sundays expected (Chat GPT) Date 1 TEMP Day of week (number) Day of week (name) Day of week (number) (#1) Day of week (name) (#1) Days diff calculated Weeks diff calculated Monday Tuesday Wednesday Thursday Friday Saturday Sunday Days without Sundays fully calculted Days without Sundays (Proof 1 GroupBy) Days without Sundays (Proof 1 manually counted)
2021-05-01 2021-06-03 34 29 2021-04-30 6 Saturday 4 Thursday 34 4 5 5 5 5 4 5 5 30 29 29
2021-07-13 2022-08-31 415 356 2021-07-12 2 Tuesday 3 Wednesday 415 59 59 60 60 59 59 59 59 356 356 356
2021-11-15 2022-01-22 69 60 2021-11-14 1 Monday 6 Saturday 69 9 10 10 10 10 10 10 9 60 60 60
2022-02-02 2022-03-20 47 40 2022-02-01 3 Wednesday 7 Sunday 47 6 6 6 7 7 7 7 7 41 40 40
2022-04-15 2022-07-06 83 71 2022-04-14 5 Friday 3 Wednesday 83 11 12 12 12 11 12 12 12 72 71 71
2023-01-04 2023-02-06 34 29 2023-01-03 3 Wednesday 1 Monday 34 4 5 4 5 5 5 5 5 30 29 29
2023-01-19 2023-03-13 54 46 2023-01-18 4 Thursday 1 Monday 54 7 8 7 7 8 8 8 8 47 46 46
2023-01-25 2023-01-30 6 5 2023-01-24 3 Wednesday 1 Monday 6 1 1 0 1 1 1 1 1 5 5 5
2023-01-25 2023-02-06 13 11 2023-01-24 3 Wednesday 1 Monday 13 1 2 1 2 2 2 2 2 12 11 11
2023-01-30 2023-02-03 5 5 2023-01-29 1 Monday 5 Friday 5 0 1 1 1 1 1 0 0 5 5 5
2024-04-07 2024-04-08 2 1 2024-04-06 7 Sunday 1 Monday 2 1 1 0 0 0 0 0 1 1 1 1

Happy Kniming
Mike

4 Likes

if you are into scripting there was a function in numpy for this (so python node required)

``````import numpy as np
import datetime as dt
start = dt.date(2024, 1, 1)
end = dt.date(2024, 1, 16)
days = np.busday_count(start, end)
print(days)
``````

br

2 Likes

Hello dear @tomljh, @mwiegand, @Daniel_Weikert. Hope you´re doing great.

I really apreciatte all your effort and solutions, also I didn´t express my self properly, so I apologize for that.

The thing I need is to compare dCol1 with dCol2 and substract 24H or 1 day if there´s a Sunday in that time range.

date1 date2 duration
25/04/2024 29/04/2024 3
27/04/2024 29/04/2024 1
26/04/2024 30/04/2024 3

Also I´m trying some ideas I get from your solutions, like using loops, of javaSnippet, but I haven´t found the trick again, I apreciate your time.
Happy weekend

BR - N

@ttsk8der you can use the workflow I provided and either remove 1 day for each Sunday or just one if a Sunday is within that date range. Give it try yourself, you’ve got everything you need

2 Likes

Hello dear @mwiegand, hope you´re great : D.

So I´m following your WK step by step but I got an issue in the first step, so the next ones are a complete mess.

Extracting fields from “Extract Date&Time Fields”. Data is does not make sense, for example.

I have the following sample.

D1 D2 weekDay numberDay
22-01-2024 22-01-2024 2 Monday
18-01-2024 19-01-2024 5 Thursday
26-01-2024 30-01-2024 6 Friday

The matter here is that, numberWeek appears with one extra number, don´t know if there´s a trouble ocasionated by the previous data process, or if it´s caused by the data type (Date & Time).

I tried to substract the aditional 1 by Math formula or
Rule engine, but any code worked, it just appended a column with -1 Value. I also would appreciate if you can share me the right process or code to solve this issue.

Many thanks.

BR

• N

Hi @ttsk8der,

Best
Mike

Hello @mwiegand : D

Sure, let me show you the WK and some sample data, also allow me to reformulate my case.
Context: Those are some of the date where my partners did some company process, the problem is, inputs are a mess because the hours are very random. I need to organize the time the process took in laboral hours, which mean that duration outputs should´nt count hours outside or sundays.

I was hoping to do it with some examples but I got more confuse so, I apologize for that.

Inside Time ops

I have a huge data set like.

And I´m trying to find the difference between all dates. BUT the difference must consider

• Calculate difference only in laboral shift
• Ignore all sunday duration until next date

I´ve tried:

• Substract some hours, but it gets messy when there are more tha 1 day of diference

BR
-N

Hi @ttsk8der,

thanks for the additional details. Do you mind sharing some sample data … just a few rows that contain your problem you’d like to resolve.

Best
Mike

1 Like

Hi @mwiegand.

I´m sorry, I forgot to attach the sample data, sure here is
Samp date.xlsx (8.4 KB)

1 Like

Awesome, thanks! Is my assumption correct that each row represents one experiment and thus the date&time stamps of each row should not get separated from each other?

1 Like

Yes that´s right.

Every row is the moment when some process was made.
I´m doing it w/o separate the time stamps form the dates, but there´s no problem if you need to separate it

Hi @ttsk8der,

does this do the trick?

Best
Mike

2 Likes

Hi @mwiegand.

Thanks for your time and efforts, honestly you gave me some ideas, like substract previous duration.

But honestly I got lost, specially because in my dataset when I´m trying to “Extract Date&Time fields” I got error on the fields with local time. Let me give you an example.

If I select “Locale configuration” in english I got the right week number for the rigth day name , as we can see here:

But if I select any “Locale configuration” in spanish (Have to make my WK in that language) I got the wrong week number for the rigth day name.

With this configuration I´m getting “Jueves (Thursday)” as 5 (Friday), or “Lunes (Monday)” as 2 (Tuesday).

Don´t know if this is a node bug or some issue ocasionated by previous processing .

Again, thanks for your time and efforts.

-N

Hi @ttsk8der,

the reason for that discrepancy is the different start of the week depending on the local time.

If you really wanna feel the pain … watch this

Best
Mike

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.