Next Monday routine not working

[pambroben]

Today is 13 May and this code is supposed to return the date of next Monday (19 May)

IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))

but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)

My begin week is Sunday (1) ,
Any ideas?

Cheers,
Brodie

 

[pr2-eugin]

How about a switch of this formula?

IIf(Weekday(Date()) = 1, Date() + 1, Date() + (9-Weekday(Date())))

 

[pambroben]

That did it.

Obviously 'rithmatic is not my strong point.

Curious to know why that worked but the other didn't.

Cheers,

 

[pr2-eugin]

That did it.

Did a little bit of testing, the code given in Post #2 will fail. Here is a better code.

Public Function getMonDate(Optional inputDate) As Date
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If IsMissing(inputDate) Or IsNull(inputDate) Then inputDate = Date
    
    getMonDate = inputDate - Weekday(inputDate, vbMonday) + 8
End Function

Copy the function into a standard module and then use it anywhere you like after you perform a Save and Compile.

 

[pambroben]

Seems to work OK for me - haven't tested it on sunday yet.

BTW I'm using it in an update query.

Cheers,
Brodie

 

[pr2-eugin]

I have performed a test over a range of 20 days back and forward. The code in Post#2, resulted in random result for everyday of the week, the code in Post#4 resulted in the correct result. I suggest you go for the second code (in Post#4).

 

[pambroben]

Hmmm.... I cannot fault your first suggestion (post #2). I had forgotten about BODMAS (brackets before addition/subtraction).

My IIF statement takes care of the Sunday situation where the next monday is the next day. I then use your code in the section half which takes care of all the other days.

Cannot fault it, so... many thanks.

Cheers,
Brodie

 

[gemma-the-husky]

Today is 13 May and this code is supposed to return the date of next Monday (19 May)

IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))

but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)

My begin week is Sunday (1) ,
Any ideas?

Cheers,
Brodie

in general terms, I think that code that is adding 9 days to anything looks a bit arbitrary and suspect.

in this case, the last term Date()-(Weekday(Date())+9)) is surely going to back up a given number of days.
maybe the intention was

(Date()-Weekday(Date())+9

not sure if that works.

 

[pambroben]

You're right, thats why I changed to pr2-eugen code in post #2.

It works great.

Cheers,
Brodie