Date Comparison (Finding removal dates that are > +1 working day after leavers date)

[thompo511]

Hi all, hope you are well.

I'm currently trying to compare two dates within a dataset to identify users who have their access removed after their leaving date. As per the agreed SLA, all access should be removed within 1 day of their last day. This becomes difficult to identify when users are leaving on a Friday, as the next working day spills over to the next week.

Would anyone have a canvas that allows you to plug in live calendar data that could perform this comparison? 

Many thanks,

Ben

[ShankerV]

Hi @thompo511 

Please find the expected output.

IF DateTimeFormat([Field1],"%a")="Fri"
THEN DateTimeDiff([Field2],DateTimeAdd([Field1],2,"days"),"days")
ELSE DateTimeDiff([Field2],[Field1],"days")
ENDIF

[ShankerV]

@thompo511 

Tried one more way to see how the result can be achieved.

Check if this works for you.

Between 10Feb and 13Feb, there is Saturday and Sunday then the Days is counted as 1 and SLA MET.

Between 10Feb and 14Feb, there is Saturday and Sunday then the Days is counted as 2 and SLA NOT MET.

Many thanks

Shanker V

[thompo511]

Hi,

Thanks for your help!!

The attached dates are part of my starting file. I cannot see the data past the 'Generate rows' tool. Have I configured the connection properly. Could you assist me with adding the file correctly. Thanks

[ShankerV · Accepted solution]

Hi @thompo511 

You have done the connection's correctly. But the date from the Input file you are used is not in Alteryx recognized date format.

Alteryx recognizes YYYY/MM/DD.

Hence I have converted the incoming date to Alteryx recognizable date format.

Many thanks

Shanker V

[thompo511]

Perfect, Thank you so much!