Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

DATETIMEDIFF discouting weekend days how to?

Joker_Hazard
11 - Bolide

Hi all.

I am trying to use datetimediff on week business day only.  However, its getting weekend days as well.

To summarize, my desired output would have to be: 

Assuming today's date (14th of june), with [DATA] like 2022-06-13 and 2022-06-10 with this formula: 

 

DateTimeDiff([Datetimetoday],[DATA],'days')


Wrong output: 4
Correct output: 2

The formula should not account for saturday and sunday. Is it possible to remove those days?

Thank you very much.

 

Joker_Hazard_0-1655217073333.png

 

2 REPLIES 2
DataNath
14 - Magnetar

Hey @Joker_Hazard, how does this look? The workflow:

 

  • Generates rows for each day between [DATE] and today's date
  • Filters out those where the weekday = Saturday or Sunday
  • For each ID, counts the number of (business) days

Let me know if this isn't what you wanted and I can revisit!

 

DataNath_0-1655217773839.png

 

Adrian_T
Alteryx
Alteryx

Hi @Joker_Hazard,

 

This formula should do the trick.

 

Since Alteryx counts Sunday as the first day of the week, we first want to identify if a [Date] falls on a Sunday or not.

- If it falls on a Sunday, our week numbers used in the formula should be reduced by 1.

 

The main formula is as such:

IF [Corrected Today Week Number] = [Corrected Data Week Number]
THEN DateTimeDiff(DateTimeToday(),[Date],'days')
ELSEIF [Data Day of the Week]=7
THEN [Difference btw Today and Date]-(2*([Corrected Today Week Number]-[Corrected Data Week Number]-1))-1
ELSE [Difference btw Today and Date]-(2*([Corrected Today Week Number]-[Corrected Data Week Number]))
ENDIF

 

Adrian_T_0-1655220605190.png

 

Labels