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

Tech-Archive recommends: Speed Up your PC by fixing your registry

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")

--
Regards

Roger Govier


"alexl" <alextheblade@xxxxxxxxx> wrote in message
news:1150146134.053191.134940@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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



.