Free Trial

Alteryx Designer Desktop Discussions

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

Subtracting dates

Able4
7 - Meteor

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

8 REPLIES 8
shancmiralles
11 - Bolide

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

OTrieger
12 - Quasar

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. 

Able4
7 - Meteor

i indicated months in the formula and the dates are set to date data type.

Able4
7 - Meteor

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. 

Able4
7 - Meteor

The format is 30/04/2023 and 31/03/2023

OTrieger
12 - Quasar

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. 

apathetichell
19 - Altair

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:

Read More
  • 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.

Able4
7 - Meteor

This was insightful. after understanding this i managed to convert the dates as mm-yyyy since i didn't need the actual date. 

Labels
Top Solution Authors