Create a calculated field:
Then colour true and false accordingly.
Create a calculated field IF [DATE]< [Current Fiscal Quarter] THEN 'green' ELSE 'orange' END
I expect you'll need to create another calculated field to calculated the Current Fiscal Quarter based on whatever rules you have for that.
Drop the field on to the Colour shelf and modify the colours so green is actually coloured green and orange is actually coloured orange.
Attached is a very basic workbook to get you going. (8.2 workbook. It will upgrade to any version you need.)
It uses DATEPART() to figure out the quarter.
You will have to modify this to handle quarters that are before/after TODAY(). The DATEPART function just returns a number from 1-4. But this shows you how to discern raw quarter values, and you can take it from there.
Quarters.twbx 20.8 KB
It is working partly.
It is taking the Calender Year Quarter. I need it to take my companies Fiscal Quarter.
i'm confused they are calender quarters, just labelled differently 1 off (i.e calendar quarter 1 is your FQ2) so the requirement for same quarter as today would still work?
if it is a labelling thing you can right click on the date field and go to default properties > Fiscal Year start > October
Hey Joe in your sheet only Lizbeth is coming as green. Toby should also be Green.
Very true, Aman.
As I noted in my reply, the calcs as they stand just return a number from 1 through 4. They don't care about which year the quarters fall in. You'll have to modify that accordingly. I just skipped over the details of doing that.