alteryx Community

# Alteryx Designer Desktop Discussions

## calculating and understanding the date time expression

11 - Bolide

Hi Team,

Hope you are well.

I need help.

I have two date fields.

a) Cntr_creation_date

b) Issue date

My workflow should have a formula tool which can calculate and display records  that were entered more than 2 days
(excl. weekends and public holidays) after the Cntr_creation_date.  Means the contracts which are entered in the system after 2 days of actually creating the contract.

1) ((DateTimeDiff([ISSUEDATE],[Cntr_creation_date],"days")*5 -

2) (ToNumber(DateTimeFormat([Cntr_creation_date], "%w")) -

3) ToNumber(DateTimeFormat([ISSUEDATE], "%w")))*2) / 7) +

4) IIF(DateTimeFormat([ISSUEDATE], "%w")=="6",-1,0) +
5) IIF(DateTimeFormat([Cntr_creation_date], "%w")=="0",-1,0)

Thanks

SR

3 REPLIES 3
20 - Arcturus
11 - Bolide

Perfect. This is great @binuacs Thank you .

Sorry couple of questions (may be very basic) please,

1) DateTimeDiff([ISSUEDATE],[Cntr_creation_date],"days")*5 - We multiply it by 5 because its (5 business days a week)  right?

2) ToNumber(DateTimeFormat([ISSUEDATE], "%w"))*2 / 7 -  Any reason why we multiply it by 2 and divide by 7?

Thanks,

Sr

20 - Arcturus

1. Yes

2. It is very hard to explain only the part of the expression logic, since all these expressions are related you need to combine all the expressions and verify based on the data.

Labels