Date Time formula error

[ivoiculescu2020]

Hello team,

I need to run a report that contains all lots that have an expiry date <= to the current date+3 months.

For example, if I run the report today and the lot expiry date is 09-Mar-2024, this lot should NOT show up. 

However, if I run the report today and the lot expiry date is 09-Sep-2023, it should show up on the report.

I tried this formula

if [DateTime_Out]<=
DateTimeFormat(DateTimeAdd(DateTimeParse(DateTimeToday(),"%d-%b-%y"),+3,"months"),'%d-%b-%Y') then "YES" else "NO" endif

but it shows me the error I attached.

Can you please advise how I can modify the formula to get the results I require?

Thank you!

Kind regards,

Ioana

[Christina_H · Accepted solution]

Take out the DateTimeParse function.  DateTimeToday outputs in date format, yyyy-MM-dd, and you're then trying to convert it from dd-Mon-yyyy into yyyy-MM-dd.

if [DateTime_Out]<=DateTimeFormat(DateTimeAdd(DateTimeToday(),+3,"months"),'%d-%b-%Y') then "YES" else "NO" endif

Also, you're then converting back to a string (DateTimeFormat) before the comparison?  That won't give you the right results.  If DateTime_Out is also in date format, you can also drop the DateTimeFormat function.

if [DateTime_Out]<=DateTimeAdd(DateTimeToday(),+3,"months") then "YES" else "NO" endif

[JamesCharnley · Accepted solution]

Trying to format within here is causing unnecessary complications Based on your initial post you'll need %Y instead of %y as well with a four digit year. I think something like 

DateTimeParse([DateTime_Out],"%d-%b-%Y") <=
datetimeadd(DateTimeToday(),3,"months")

is what you're looking for.

[ivoiculescu2020]

Hello @Christina_H and @JamesCharnley 

It worked!!!!!!

thank you so much for your prompt assistance.

Kind regards,

Ioana