Matching up Week Formats in Pivot Tables and Other Sources…

Just a quick trick before the weekend starts and I can spend some quality time with something/someone who is not a pivot table…

I mentioned in my post Two Great Tastes That Taste Great Together that you can pull TimeByDay into an Excel report and then “group” 7 days at a time to get weeks.  This function totally rules, but it does have one constraint – the format of those dates is always “startdate – enddate”, ( so a week would look like 1/1/2012 – 1/8/2012 ) and there is NO way to change it.  Trust me on this, I spent a fair amount of time trying to  use conditional formatting and number formatting to override this.  Eventually I just gave up and decided, “When in Rome…”

So to keep things straight and comparable, for many dark purposes, I found a way to force other data which is not “grouped” like that to mimic the start – end format.  It’s not terribly exciting, or hard to figure out, but I thought since I’m the one trying to lead people down this path, I might as well leave a few trail blazes behind.  This portion of my query is written for timesheets out of Project Server, but I’m sure you could use it for any manner of start/end date columns in a table.  Just stuff this shiny statement into your SELECT:

CONVERT(VARCHAR, MSP_TimesheetPeriod.StartDate+1, 101) + ‘ – ‘ + CONVERT(VARCHAR, MSP_TimesheetPeriod.EndDate+1, 101) as [Week],

That will make your weeks look like the same kind of date range as the grouped pivot table data out of Project.  I promise to stop being so boring someday.  Not everything can be as cool as Finding Documents with No Checked In Version Across a Site Collection, right?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s