Determine if day is a weekend

[#1]

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.

[#2 · Accepted solution]

you can use something like this:

if(weekday(Date)<5,Date)

best regards!

[#3]

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

[rwunderlich]

How about:

=FirstWorkDate(vDailyDate,1)
=FirstWorkDate(vDailyDate,2)
=FirstWorkDate(vDailyDate,3)

and so on.

-Rob