Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
See Calculating Months difference between two dates
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.
use networkdate() function to get the date difference.
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
Simply subtract them: interval(Date1 - Date2,'d')
Hi,
In addition to Gysbert's reply you can also try
=Num(EndDate - StartDate)
Hope this helps you.
Regards,
Jagan.
use interval() function
try makedate function also
Thanks for the help guys this will help me.