can I get a pivot table to show only data during working hours

[alexl]

Hi,

can I get a pivot table to show only data with the date field during
working hours e.g. 9am-5pm mon-fri excluding holidays

thanks

 

[Peo Sjoblom]

Not from raw time and date data, you would need somehow to append to the
source data

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Roger Govier]

Hi Alex

You would need to create another column in your raw data, carrying out
the test, and using this filed as the date filed in your PT. Do bear in
mind that you cannot have blank date fields if you need to group the
data by date, so you could just use a date outside of the range you are
dealing with, for data that doesn't match, then just exclude that date
range from the PT.

For example
=IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>=TIME(9,0,0),MOD(A1,1)<=TIME(17,0,0)),A1,"01/01/1900")

 

[alexl]

thanks, inspired by your idea I I ended up using

=AND( (HOUR(a1)>9), (HOUR(a1)<17),(WEEKDAY(a1)>1), (WEEKDAY(a1)<7) )

then in pivot table just show where field = TRUE

 

[Roger Govier]

Nice solution, thanks for posting back.