Convert a time on weekend day to week day 1

[ccanalia]

I am running CR10 and using a SQL Server Database. I have this formula so far.

if time({Cases.CreateDate}) <= ctime(9,0,0) then
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),day({Cases.CreateDate}),9,0,0)
else
{Cases.CreateDate}

What I want to incorporate into this formula is that if the CreateDate happens to fall on a weekend, I want it to also change the date to the next Monday. Is this possible?

 

[lbass]

Try:

if dayofweek({Cases.CreateDate}) in 1 to 5 then
(if time({Cases.CreateDate}) <= ctime(9,0,0) then
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0) else
{Cases.CreateDate}) else
if dayofweek({Cases.CreateDate}) in [6,7] then
(if time({Cases.CreateDate}) <= ctime(9,0,0) then
dateadd("d", 7 - dayofweek({Cases.CreateDate})+1,
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0)) else
dateadd("d", 7 - dayofweek({Cases.CreateDate})+1,
{Cases.CreateDate}))

-LB

 

[ccanalia]

I tried this but I am getting the same thing as I was before. I have One date that is showing up on a Sunday (10/31/04 3:06pm) and the same date and time shows up whether I use the formula I provided or the formula that was suggested. Any other suggestions?

 

[lbass]

Sorry, I was thinking that Monday was the default first day of the week, but Sunday is. Change the formula to:

if dayofweek({Cases.CreateDate},crMonday) in 1 to 5 then
(if time({Cases.CreateDate}) <= ctime(9,0,0) then
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0) else
{Cases.CreateDate}) else
if dayofweek({Cases.CreateDate},crMonday) in [6,7] then
(if time({Cases.CreateDate}) <= ctime(9,0,0) then
dateadd("d", 7 - dayofweek({Cases.CreateDate},crMonday)+1,
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0)) else
dateadd("d", 7 - dayofweek({Cases.CreateDate},crMonday)+1,
{Cases.CreateDate}))

-LB

 

[ccanalia]

Ive gotten clsoer but the problem that I am running into is when the day is changed to the first monday, it is not changing the time to 9am on Monday. I have played around with the formula a little but so far I have not gotten what I am trying for.
Example:
I have One date that is showing up on a Sunday (10/31/04 3:06pm) and after I apply the formula I end up with (11/01/04 3:06pm). What else do I need to do in order to make the time 9:00am on that Monday?

 

[lbass]

if dayofweek({Cases.CreateDate},crMonday) in 1 to 5 then
(if time({Cases.CreateDate}) <= ctime(9,0,0) then
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0) else
{Cases.CreateDate}) else
if dayofweek({Cases.CreateDate},crMonday) in [6,7] then
dateadd("d", 7 - dayofweek({Cases.CreateDate},crMonday)+1,
cdatetime(year({Cases.CreateDate}), month({Cases.CreateDate}),
day({Cases.CreateDate}),9,0,0))

-LB

 

[ccanalia]

That worked like a charm. Thank you soooo much.