Alteryx Designer Discussions

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

Date formatting

Albert2107
7 - Meteor

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.

Albert2107_0-1636620495536.png

 

 

Warm regards,

Albert

11 REPLIES 11
FláviaB
Community Manager
Community Manager

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

Flávia Brancato
Luke_C
14 - Magnetar

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
9 - Comet

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
7 - Meteor

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
7 - Meteor

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
11 - Bolide

@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
7 - Meteor

Thank you @Christina_H this one works!

 

Warm regards,

Albert

Albert2107
7 - Meteor

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

Albert2107_0-1637733802554.png

 

For visualization of the formula: 

Albert2107_1-1637734705170.png

 

Christina_H
11 - Bolide

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:

Christina_H_0-1637745885629.png

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

Labels