Hi there I'm trying to do some report automation, and what I need is to return any record where the date is in the current quarter or the most recent three quarters before that. So for example, with the date today being 29th Jan 2014, we are in Q1 2014, I would therefore need any records returning where the Date is between 01/04/2013 and 29/01/2014 (and hence being Q1 2014, Q4 2013, Q3 2013 or Q2 2013).
I have used the following formula (which works):
=IF((YEAR(TODAY())&" "&"Q"&ROUNDUP((MONTH(TODAY())/3),0))=U2,"yes",IF((YEAR(TODAY()-90)&" "&"Q"&ROUNDUP((MONTH(TODAY()-90)/3),0))=U2,"yes",IF((YEAR(TODAY()-182)&" "&"Q"&ROUNDUP((MONTH(TODAY()-182)/3),0))=U2,"yes",IF((YEAR(TODAY()-273)&" "&"Q"&ROUNDUP((MONTH(TODAY()-273)/3),0))=U2,"yes","no"))))
But then when I pivot the data, the columns are in an odd order:
2013 Q2, 2013 Q4, 2013 Q3, 2014 Q1. I need them in order from left to right oldest quarter to newest quarter.
Any pointers?
Thanks
stixmcvix
I have used the following formula (which works):
=IF((YEAR(TODAY())&" "&"Q"&ROUNDUP((MONTH(TODAY())/3),0))=U2,"yes",IF((YEAR(TODAY()-90)&" "&"Q"&ROUNDUP((MONTH(TODAY()-90)/3),0))=U2,"yes",IF((YEAR(TODAY()-182)&" "&"Q"&ROUNDUP((MONTH(TODAY()-182)/3),0))=U2,"yes",IF((YEAR(TODAY()-273)&" "&"Q"&ROUNDUP((MONTH(TODAY()-273)/3),0))=U2,"yes","no"))))
But then when I pivot the data, the columns are in an odd order:
2013 Q2, 2013 Q4, 2013 Q3, 2014 Q1. I need them in order from left to right oldest quarter to newest quarter.
Any pointers?
Thanks
stixmcvix