Comparing dates and using an if statement

[#1]

Hi there,

I am trying to compare today's date to see if it falls within our period quarter and if the period quarter is in the past, I want to do another calculation instead. I think that the date comparison is incorrect in my code but I can't figure out what I am doing wrong. So, I have made sure that the date I am comparing is set up as a Date in the format 'YYYYMMDD'

=if(date(today(0),'YYYYMMDD')<[Period Quarter End Date], Calc1,Calc2)

Could you please tell me where I am going wrong?

Thanks, Mike

[#2]

Hi,

please provide some data.

Thank you!

Rainer

[Miguel_Angel_Baeyens]

Hello Mike,

It should be as simple as:

=if(date#(today(0),'YYYYMMDD') < date#([Period Quarter End Date],'YYYYMMDD'), Calc1, Calc2)

Note the date#() function instead the date() function to force comparing to a timestamp.

But I'd suggest you to use a different function:

=if(InQuarter(Today(),date([Period Quarter End Date]),0), Calc1, Calc2)

Hope this helps.

[#4 · Accepted solution]

Thanks for all the help, unfortunately, I cannot use the InQuarter function as we are using our periodic months, not calendar months.

I found out that the data has multiple period end dates so a simple date#([Period Quarter End Date],'YYYYMMDD') produces a null value so I used

=if(date(today(0),'YYYYMMDD') < date(max({$<[Period Quarter]={'Q1'}>}[Period Quarter End Date]),'YYYYMMDD'), calc1,calc2)

Hope this helps other people.

Mike