Date formatting

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

Date Time

Accepted answers

Christina_H

@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'.

Christina_H

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

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Count-workday-difference-between-2-dates/td-p/92579

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).

All comments

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")

Capture.PNG

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

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:

WFH sample data.xlsx

Christina_H

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

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Count-workday-difference-between-2-dates/td-p/92579

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)