Date Calculation

[adyf]

FM accepts the following calculation but it's not doing what I require it to. I'm trying to conditionally format a date field if the current date minus 3 years is >= TOP 006 Records 3::Most Recent Date.

 

What's glaringly wrong, the first part of my calculation between Day, Month etc won't accept ; hence &, but the second part does?

 

Day (Get (CurrentDate)) & Month (Get (CurrentDate)) & Date(Year( Get (CurrentDate) - 3) >= Day ( TOP 006 Records 3::Most Recent Date ); Month (TOP 006 Records 3::Most Recent Date );Year (TOP 006 Records 3::Most Recent Date ))

 

 

 

[comment]

Try =

Date ( Month ( Get(CurrentDate) ) ; Day ( Get (CurrentDate) ) ; Year( Get(CurrentDate) ) - 3 ) ≥ TOP 006 Records 3::Most Recent Date

or =

Let (
today = Get (CurrentDate)
;
Date ( Month ( today ) ; Day ( today ) ; Year ( today ) - 3 ) ≥ TOP 006 Records 3::Most Recent Date
)

[adyf]

Thanks comment. So I erroneously thought TOP 006 Records 3::Most Recent Date needed breaking down into Day, Month and Year.

 

Also TOP 006 Records 3::Most Recent Date is formatted dd/mm/yyyy. Do I have to reverse the Month and Day in your calculation?

[comment]

So I erroneously thought TOP 006 Records 3::Most Recent Date needed breaking down into Day, Month and Year.

 

That's right: it's already a date and doesn't need to anything to be done to it. Get (CurrentDate) is also a date - but in order to subtract 3 years from it, you need to:

1. Break it up to individual elements;

2. Subtract 3 from the Year element;

3. Recombine the elements into a date.

It was step #3 that was the major issue in your attempt; you tried to concatenate them as text, instead of plugging them into the Date() function which was written specifically for this purpose.

 

 

Do I have to reverse the Month and Day in your calculation?

No. The order of parameters of the Date() function is universal.

[adyf]

That's right: it's already a date and doesn't need to anything to be done to it. Get (CurrentDate) is also a date - but in order to subtract 3 years from it, you need to:

1. Break it up to individual elements;

2. Subtract 3 from the Year element;

3. Recombine the elements into a date.

It was step #3 that was the major issue in your attempt; you tried to concatenate them as text, instead of plugging them into the Date() function which was written specifically for this purpose.

 

 

No. The order of parameters of the Date() function is universal.

 

Thank you very much for your explanation.