Weekend Date Transformation

I needed a calculated field in one of my reports that had logic to tell if a "CreatedDate" field fell into the Weekend window for a particular department in my company. If the field was between friday 5pm and sunday 11pm it was considered the weekend and the New date given would be start of business sunday at 11pm.

This query takes the date... transforms it to a number value representing the day of the week. it then strips off the date to a known date of january 1, 0001 and leaves the time... next a datediff in seconds calculates the amount of seconds needed to alter later add to the date to make it a finished product.

I was not given much time for polishing... but my testing shows it works. It probably could be improved... but hey here is a general direction if you face the same problem I did.

Here is some code that I wrote up simular to what you mentioned...I wanted to take a field called "CreatedDate" and if it fell into our window of what we call a weekend to show it as the next business day which would be Friday 5:00PM thru sunday 11:00PM.It will have to be altered to fit your needs...

=iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <=
25199),IIF(Weekday(Fields!CreatedDate.Value) = 6 , DateAdd("S"
,iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0") + (iif(iif((Weekday(Fields!CreatedDate.Value) = 6 and
Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM")
<= 25199) or (Weekday(Fields!CreatedDate.Value) = 7) or
(Weekday(Fields!CreatedDate.Value) = 1 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM") >=1),
Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM"),
"0") >= 1,172800,0)), Fields!CreatedDate.Value), ""),"")
&
IIF(Weekday(Fields!CreatedDate.Value) = 7,
IIF(Weekday(Fields!CreatedDate.Value) = 7, DateAdd("S"
,(iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0") + 86400), Fields!CreatedDate.Value) , ""),"")
&
IIF((Weekday(Fields!CreatedDate.Value) = 1) and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00 PM") >=1,
IIF(Weekday(Fields!CreatedDate.Value) = 1, DateAdd("S"
,iif((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") <= 25199) or
(Weekday(Fields!CreatedDate.Value) = 7) or (Weekday(Fields!CreatedDate.Value) =
1 and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") >=1), Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001
11:00:00 PM"), "0"), Fields!CreatedDate.Value), ""),
"")&
IIF((Weekday(Fields!CreatedDate.Value) = 6 and Datediff("S",
TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:59:59 PM") >
25199),Fields!CreatedDate.Value,"")&
IIF((Weekday(Fields!CreatedDate.Value)
= 1) and Datediff("S", TimeValue(Fields!CreatedDate.Value),"1-JAN-0001 11:00:00
PM") < 1, Fields!CreatedDate.Value,
"")&
IIF(Weekday(Fields!CreatedDate.Value) = 2 or
Weekday(Fields!CreatedDate.Value) = 3 or Weekday(Fields!CreatedDate.Value) = 4
or Weekday(Fields!CreatedDate.Value) = 5, Fields!CreatedDate.Value,"")