Excel – Quarter/Year from Date

So you want to display the Quarter Number, Q1,Q2 etc along with the Year in the format Q1-11 Q2-10 etc.

This is a handy formula which will allow you to group all your dates in the relevant quarter of the year.
I found this particularly useful for an Xcelsius dashboard, which needed to group and filters data into Quarters.

Enter your date field in A1 and place the forumla below in the require cell.

=”Q”&INT((MONTH(A1)-1)/3)+1&”-“&RIGHT(YEAR(A1),2)

1/1/2011  Q1-11

Crystal Reports Previous Month Calculation

I needed to get a report to run via Business Objects scheduler which would return data for the previous full month. So a Report run on 1st Feb, would return all data within a date range of 1st Jan – 31st Jan.

But the report would also need to be able to run on specific dates if the user required.

Step 1 – Create “Previous Month” Parameter”.

So I created a parameter which was a simple Y/N drop down. If “Y” was selected it would return the data for the previous Month.

{?CurMonth}

If N was selected, the report would use a Start Month and End Month Parameter values to return the required data.

Step 2. – Create Forumla to work out Previous Full Month Start/End Date 

Create forumla @strFDMonth
This will calculate the First Day of the Previous Full Month.

minimum(Lastfullmonth)

Create forumla @strLDMonth
This will calculate the LadDay of the Previous Full Month.

maximum(Lastfullmonth)

Step 3 – Enter Selection Criteria

If {?CurMonth} = ‘Y’ Then
{DATE_FIELD} >= {@StrFDMonth} and {DATE_FIELD}} <= {@StrLDMonth}
else
{DATE_FIELD}} >= cdate({?STARTDATE}) and {DATE_FIELD} <= cdate({?ENDDATE})