Problem comparing dates

[robstar256]

Hey everyone,

I'm trying to do something that seems to be simple, but I can't get the dates to ever be equal.

Here's a simple table:

PROJECTSTATUS:
Load PROJECTID,
PROJECTNUMBER,
STATUS,
STATUSUPDATE,
date(HIDATE, 'M/D/YYYY') as HIDATE,
Time(HIDATE) as HITIME;

I want to create a flag that shows 1 or 0 in a table column depending on if the project status was updated this month or not.  I can't seem to get the date to match even with something simple like:

=if( [HIDATE] = '6/27/2022', 1, 0)  or   =if([HIDATE] = date(6/27/2022), 1, 0)

it's always false:

Appreciate any ideas on how to get these to equate!  Thanks!

[sidhiq91]

@robstar256  I used below data as sample for my front end evaluation:

NoConcatenate
Temp:
Load * inline [
HIDate
6/27/2022
6/25/2022
6/26/2022
6/23/2022
06/22/2022
];

Exit Script;

Backend Expression in the Straight table:

=if(Date(Date#(HIDate,'MM/DD/YYYY'),'MM/DD/YYYY')=Date('6/27/2022','MM/DD/YYYY'),1,0)

 

[robstar256]

Thanks!  that works for the hardcoded date.   Now for my real world example, I have a rolling cuttoff date in a variable called vCutOffDate.  I was hoping something like this would work, but of course it doesn't match:

SET vCutOffDate = MakeDate( Year(Today()), Month(addmonths(max(HIDATE),-1)),27);  //  =5/27/2022

=if(Date(Date(HIDATE,'MM/DD/YYYY'),'MM/DD/YYYY') >= Date(Date($(vCutOffDate),'MM/DD/YYYY'),'MM/DD/YYYY'),1,0)

any ideas?  Thanks!

[sidhiq91]

@robstar256  Any reason why you are hardcoding it to '27', Is your rolling cut off date always 27th of each month? or the last working day?

[robstar256]

Hi, we have a cutoff on the 27th for project managers to enter updates.   Any idea how to make your statement work with a variable?  

thanks!