Subtracting dates
- 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,
Am trying to subtract months, however, when i subtract March 2023 and April 2023 it gives me zero, yet i expect to get 1. I used the DateTimeDiff()
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
hi.. if your format is March 2023 and April 2023 then highly likely your data type for both is string... you need to use date time tool to convert them first to date types..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Able4
Which units did you set in the formula?
DateTimeDiff(dt2,dt1,u)
It should be "Months" to indicate which periods need to be used in the calculation. You need to define all 3 parameters.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
i indicated months in the formula and the dates are set to date data type.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
they are already set to date data type. The formula returns the correct number of months for all other months with exception of when it is subtracting April 2023 and March 2023.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The format is 30/04/2023 and 31/03/2023
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I see.
Can you add a snippet of the result window for that Formula Tool for these dates and some other dates that worked well.
As if it is working well with other periods it means that you have an issue with that specific line of data.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
So Month is not performing as you might expect it to here - you are looking at - has a month elapsed since March 31st. Let's pretend the date was the 15th - not the 31st. Would you say that April 14th is one month after March 15th? No - you would say that April 15th is one month later. Let's extend this logic to March. Without any external knowledge - one would expect the the one month mark at the impossible April 31st. Applying the same logic - Alteryx says April 30th is not one month later. It is 30 days later. It is the last day of April - but it is not one month later. Perhaps you want to look at Month vs Month instead of Day vs Day? Or compare last day to last day? Alteryx discusses this in the notes on datetime:
For Month and Year differences, a month is only counted when the end day reaches the start day, ignoring the time of day.
https://help.alteryx.com/current/en/designer/functions/datetime-functions.html#example-6575921-2
note this is a contrast with datetimeadd("2023-03-31",1,"months") - which will show up as 2023-04-30.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This was insightful. after understanding this i managed to convert the dates as mm-yyyy since i didn't need the actual date.
