Irregular date periods

mrhk22

New Member
Joined
Jul 10, 2016
Messages
21
I'm working with a power pivot table where the source data contains two columns for year and period, like this:

2020 FY
2020 H1
2020 H2
2020 Q1
2020 Q2
2020 Q3
2020 Q4

This is to indicate when a client satisfaction survey was completed. Different clients submit them for different intervals of service. In the pivot table, for each client (rows), I'd like to have two columns - the first showing if a satisfaction survey was received (Yes/No) and the second one indicating the most recent survey received.

What is the best way to set this up with a sort order to return the correct period? Is it necessary to convert each interval into a period end-date?
Normally if there is both a FY and Q4 survey on record, the FY would take priority, but this can be ignored if it's too complext. Thank you for any advice.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top