Date formatting

[Albert2107]

Hi All,

I am new in Alteryx and this is just a very basic question. I am trying to calculate the no. of working days between two dates. I am wondering how is the correct format on writing a date in the formula tool? this is what my formula looks like and it doesn't calculate correctly or it errors since the supposedly dates (8/1/2019 and 7/31/2020) are not in date format. 

See image below.

Warm regards,

Albert

[FláviaB]

Thank you for your post, @Albert2107. Since this board is meant for new members introductions, you don't get the same visibility as if you post in our Designer Discussions board. I am moving your post there so you can have a proper support. 😉

[Luke_C]

Hi @Albert2107 

You'll want to make sure that your date fields are set as a 'date' data type. They would look like '2021-01-21'. Your formula isn't working because Alteryx won't recognize mm/dd/yyyy as dates. You can use a datetime tool or datetimeparse function to convert the data. 

Once converted to proper dates, you would type them into the formula tool the same way, in quotes: '2021-01-21'. 

If you have some sample data I can mock something up.

[Kamran1991]

Hi @Albert2107,

The input dates must be recognizable by Alteryx then you can calculate the number days between two date by using this formula.

DateTimeDiff([End date],[Start date],"Days")

[Albert2107]

Hi @Luke_C 

I did not use datetime tool since the 8/1/2019 and 7/31/2020 are constant dates and I just wanted to write it in the formula tool (so there's no field for these dates). As for the hire date and termination date, yes I can confirm that the data types for those fields are date. Thank you anyway on your inputs.

Warm regards,

Albert 

[Albert2107]

Hi @Kamran1991 ,

Yes I did use that formula, but my question is the format on writing dates in the formula tool, as you can see in the image above I'd like to calculate the no. of days between termination date, hire date, 8/1/2019 and 7/31/2020 (which are also dates).

Warm regards,

Albert

[Christina_H · Accepted solution]

@Albert2107  The date format in Alteryx is yyyy-mm-dd.  It will not recognise mm/dd/yyyy as a date by default.  Change your formula to replace 8/1/2019 with '2019-08-01' (including quotes) and 7/31/2020 with '2020-07-31'.

[Albert2107]

Thank you @Christina_H this one works!

Warm regards,

Albert

[Albert2107]

Hi @Kamran1991, @Luke_C, @Christina_H,

Thank you all for your help, I remember that this formula counted the number of works within hire date and termination date and/or start date (2019-08-01) to end date (2020-07-31). I am thinking how can I translate this formula (same condition applies) to count only the number of working days? I already look answers from the community but most samples are simple and compose of two files - dates and holidays/weekends. 

You may see attached file here for your reference.

Here's the updated formula condition for calculating the no. of working days

For visualization of the formula: 

[Christina_H · Accepted solution]

There's a formula for counting working days on this thread:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Count-workday-difference-between-2-dat...

Formula:

1 + ((DateTimeDiff(End,Start,"days")*5 - (ToNumber(DateTimeFormat(Start, "%w"))-ToNumber(DateTimeFormat(End, "%w")))*2) / 7) + 
IIF(DateTimeFormat(End, "%w")=="6",-1,0) + 
IIF(DateTimeFormat(Start, "%w")=="0",-1,0)

  You would just need to put in the relevant start and end dates.  In your case, I would do something like this:

I've replaced the start/end dates with the fixed dates if they are outside the range you're looking at, then replaced the formula with 0 if StartDate>EndDate (i.e. the whole thing is outside the range).