Issues with evaluating Dates

Stefano Ferretti

I need to evaluate a date in a 'target closure' column, and report the fiscal quarter.

I have created a separate sheet to help me automate it.

The Fiscal Calendar sheet has 3 simple column with Quarter Number, Quarter Start Date and Quarter End Date (screenshot below) (The start and End Date column are Date Columns)

in my main sheet then I have the Target Closure Date column and the column where I want to calculate the quarter

I have the below formula

=INDEX(COLLECT({Fiscal Quarter}, {Quarter Start Range}, <=[Target Closure Date]@row, {Quarter End Range}, >=[Target Closure Date]@row), 1)

It works like a charm except for dates falling in the FY22Q4 range.

so if my Target Closure date is the 2nd of November 2021 I have an error (invalid value), but if the date is 31st October 2021 it correctly reports FY22Q3

Any idea?

Stefano Ferretti

Scratch that. now it is working ...

Guess it's one of those mysteries ...

Stefano Ferretti

Scratch that. now it is working ...

Guess it's one of those mysteries ...