Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with 5 columns. There is a calendar selector object. Whatever day is selected is used for the first column's calculations. The next column uses the selected day minus one and so on. My problem is that I want to exclude weekends. So if 1/25/2011 is selected (which is a Tuesday) the columns will show values based off of the following dates:
1/25/2011 - 1/24/2011 - 1/21/2011 - 1/20/2011 - 1/19/2011
Before i just had a variable that equaled the selected date vDailyDate, then I did (vDailyDate-1) ... (vDailyDate -2) ... and so on for the previous days but this obviously doesn't work for excluding weekends.
Any ideas? Thanks.
you can use something like this:
if(weekday(Date)<5,Date)
best regards!
you can use something like this:
if(weekday(Date)<5,Date)
best regards!
Column 1=if(num(Weekday(Date)) < 5 ,Date,if(num(Weekday(Date))=5,Date(Date-1),Date(Date-2)) )
Column2 =if(num(Weekday(Date(Date-1))) < 5 ,Date(Date-1),if(num(Weekday(Date(Date))) =5,Date(Date-2),Date((Date-3))))
and similarly column 3 , 4 and 5
How about:
=FirstWorkDate(vDailyDate,1)
=FirstWorkDate(vDailyDate,2)
=FirstWorkDate(vDailyDate,3)
and so on.
-Rob