Date formatting
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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. 😉
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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'.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There's a formula for counting working days on this thread:
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).
