Number of months between 2 different dates

[stevietm]

Hi All

I am stuck with an expression and would appreciate any feedback regarding this problem. I have seen a lot of similar post but none of them has been solved so I want to post this again.

In my main T table I have 4 different dates next to each other in the table. My first date for example is when a patient has been diagnosed

The second date is when the patient’s treatment starts. So for this I would like to know the amount of months and days if possible between these dates.

Then I have another 2 dates, the first date is when the patient need to go for a service and when the treatment stops. So both of these dates are future dates from the treatment start date.

Is there an expression I can use to count these months and days between these dates?

Thanks for your help in advance.

Regards

Stevie

[Gysbert_Wassenaar]

See Calculating Months difference between two dates

[jagan · Accepted solution]

Hi,

Try like this

The script below calculates the months difference between two given dates.

//Function to calculate Months Difference in script

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

Now, we can use the MonthDiff as a function

Data:

LOAD

    UniqueNo,  

    StartDate,

    EndDate,

    ($(MonthDiff(StartDate, EndDate)) AS MonthsDifference

FROM DataSource;

In the above load statement we are calculating the months difference between two dates and arriving it as a new column.

We can also use the MonthDiff in expressions also like below

=$(MonthDiff(Today(), Today() + 65))

Hope this helps others.

Regards,

Jagan.

[AbhijitBansode]

use networkdate() function to get the date difference.

[stevietm]

Hi,

The function is working great for this, now i would just like to know if there is a function to calculate the exact number of days between the dates.

Thanks

Regards

Stevie

[Gysbert_Wassenaar]

Simply subtract them: interval(Date1 - Date2,'d')

[jagan]

Hi,

In addition to Gysbert's reply you can also try

=Num(EndDate - StartDate)

Hope this helps you.

Regards,

Jagan.

[vinay_hg]

use interval() function

[vinay_hg]

try makedate function also

[stevietm]

Thanks for the help guys this will help me.