Icon

78740 - Days Difference ignoring Sundays

Forum Posthttps://forum.knime.com/t/period-time-with-weekends/78740ChallengeCalculate the difference in days between two datesingoriing sundays Consiideraations- Each week has seven days- If total amount of weeks between dates is > 0 thansubstract the amount of weeks from DatedifferenceSolutionFor each full week reduce the amount of days byoneIF Weeks diff > 0 THEN Days diff - Weeks diffBack Test / Proof- Loop per row- Filter current row- Add rows for eaach date between start and end- Determine day of week- Filter Sundays (#7)- Count rows- Compare results FallbackSee ...https://forum.knime.com/t/date-time-difference-consider-starting-date-add-option-to-in-exclude-start-date/78787 Unsing Group by to Count days per Date 1 andDate 2 w/o Sundays Unsing Pivot to count Days per Day of Week andthen sum up Mon-Sat Node 1Node 2Day of Weekfrom Date 1Day of Weekfrom Date 2Weeks diff IMPORTANTUsing Date 1 TEMPresults in partially invalid results!It's unclear to me why at present.Node 6Days diff betweenDaate 1 TEMPand Date2TOPWeeks diff= 0Date 1 TEMP-1 to factor in Start Datefor CalculationRecreateTableAdd Days difffor non-calculated resultsNode 13Filter currentRowLoop forExpecteds Dys difffrom Chat GPTShift Date 1 perCurrent IterataionNode 17Node 18Day of Weekfrom Date 1 shiftedBOTTOMall SundaysNode 21Edge CaseSunday in betweenstart and end datebut no full week diffNode 23Node 24Node 25Node 26Node 27Node 28Node 29Node 30Node 31Node 32TOPDate&TimeBOTTOMDurationAdd Row IndexTo keep Date&TimetogetherLoop perRow IndexNode 36Day of Weekfrom Date 1TOPLabor ShiftBOTTOMSubdaysDays diff betweenDaate 1 TEMPand Date2TOPDuration Diffduring Labor ShiftBOTTOMSkipped SundaysRecreateTaableSanitizeColumn NamesRemoveRow Index andDuration D1Table Creator String to Date&Time Extract Date&TimeFields Extract Date&TimeFields Date&TimeDifference Math Formula Date&TimeDifference Row Splitter Date&Time Shift Concatenate Rule Engine Table Row toVariable Loop Start Row Filter Counting Loop Start Date&Time Shift Loop End Loop End Extract Date&TimeFields Row Splitter GroupBy Rule Engine Column Renamer Joiner Pivot Column Resorter Math Formula Joiner Missing Value Row Filter Column Resorter Excel Reader Column Splitter Rule Engine Group Loop Start Unpivot Extract Date&TimeFields Row Splitter Date&TimeDifference Loop End Pivot Column Rename(Regex) Column Filter Forum Posthttps://forum.knime.com/t/period-time-with-weekends/78740ChallengeCalculate the difference in days between two datesingoriing sundays Consiideraations- Each week has seven days- If total amount of weeks between dates is > 0 thansubstract the amount of weeks from DatedifferenceSolutionFor each full week reduce the amount of days byoneIF Weeks diff > 0 THEN Days diff - Weeks diffBack Test / Proof- Loop per row- Filter current row- Add rows for eaach date between start and end- Determine day of week- Filter Sundays (#7)- Count rows- Compare results FallbackSee ...https://forum.knime.com/t/date-time-difference-consider-starting-date-add-option-to-in-exclude-start-date/78787 Unsing Group by to Count days per Date 1 andDate 2 w/o Sundays Unsing Pivot to count Days per Day of Week andthen sum up Mon-Sat Node 1Node 2Day of Weekfrom Date 1Day of Weekfrom Date 2Weeks diffIMPORTANTUsing Date 1 TEMPresults in partially invalid results!It's unclear to me why at present.Node 6Days diff betweenDaate 1 TEMPand Date2TOPWeeks diff= 0Date 1 TEMP-1 to factor in Start Datefor CalculationRecreateTableAdd Days difffor non-calculated resultsNode 13Filter currentRowLoop forExpecteds Dys difffrom Chat GPTShift Date 1 perCurrent IterataionNode 17Node 18Day of Weekfrom Date 1 shiftedBOTTOMall SundaysNode 21Edge CaseSunday in betweenstart and end datebut no full week diffNode 23Node 24Node 25Node 26Node 27Node 28Node 29Node 30Node 31Node 32TOPDate&TimeBOTTOMDurationAdd Row IndexTo keep Date&TimetogetherLoop perRow IndexNode 36Day of Weekfrom Date 1TOPLabor ShiftBOTTOMSubdaysDays diff betweenDaate 1 TEMPand Date2TOPDuration Diffduring Labor ShiftBOTTOMSkipped SundaysRecreateTaableSanitizeColumn NamesRemoveRow Index andDuration D1Table Creator String to Date&Time Extract Date&TimeFields Extract Date&TimeFields Date&TimeDifference Math Formula Date&TimeDifference Row Splitter Date&Time Shift Concatenate Rule Engine Table Row toVariable Loop Start Row Filter Counting Loop Start Date&Time Shift Loop End Loop End Extract Date&TimeFields Row Splitter GroupBy Rule Engine Column Renamer Joiner Pivot Column Resorter Math Formula Joiner Missing Value Row Filter Column Resorter Excel Reader Column Splitter Rule Engine Group Loop Start Unpivot Extract Date&TimeFields Row Splitter Date&TimeDifference Loop End Pivot Column Rename(Regex) Column Filter

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: Mike Wiegand
Created at: 2024-04-25
On NodePit since: 2024-04-26
Last update: 2024-12-17
Created with KNIME version: v5.2.3

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!