How to find previous Monday (date) from specific date

[Guest]

Hi

Does anybody know how to find previous Monday date from speficied date.
For example myDate = '22-09-2004' and the previous Sunday should be
'13-09-2004'

TIA
Gabriel

 

[Nikos Yannacopoulos]

Gabriel,

It's very easy to do with a user defined function in VBA, only you're not
clear in whether you are looking for the last Monday or Sunday! Anyway, the
idea is:

Function Last_Monday_Before(vDate As Date)
If Weekday(vDate, vbSunday) = 2 Then
Last_Monday_Before = vDate - 7
Else
Last_Monday_Before = vDate - Weekday(vDate, vbSunday) + 2
End If
End Function

Or

Function Last_Sunday_Fefore(vDate As Date)
If Weekday(vDate, vbSunday) = 1 Then
Last_Sunday_Fefore = vDate - 7
Else
Last_Sunday_Fefore = vDate - Weekday(vDate, vbSunday) + 1
End If
End Function

Paste the code in a general module and save. Then you can use the function
like, for instance:

Last_Monday_Before([SomeDateFieldOrControl])

or

Last_Monday_Before(#24/09/2004#)

in any query, form, report, code procedure or macro action argument, and it
wil return the calculated date based on the parameter in the parantheses.

HTH,
Nikos

Last

 

[Guest]

Hi Nikos,

Thank for your ideas, it works greats and very appreciated for your help.

Gabriel

Gabriel,

It's very easy to do with a user defined function in VBA, only you're not
clear in whether you are looking for the last Monday or Sunday! Anyway, the
idea is:

Function Last_Monday_Before(vDate As Date)
If Weekday(vDate, vbSunday) = 2 Then
Last_Monday_Before = vDate - 7
Else
Last_Monday_Before = vDate - Weekday(vDate, vbSunday) + 2
End If
End Function

Or

Function Last_Sunday_Fefore(vDate As Date)
If Weekday(vDate, vbSunday) = 1 Then
Last_Sunday_Fefore = vDate - 7
Else
Last_Sunday_Fefore = vDate - Weekday(vDate, vbSunday) + 1
End If
End Function

Paste the code in a general module and save. Then you can use the function
like, for instance:

Last_Monday_Before([SomeDateFieldOrControl])

or

Last_Monday_Before(#24/09/2004#)

in any query, form, report, code procedure or macro action argument, and it
wil return the calculated date based on the parameter in the parantheses.

HTH,
Nikos

Last

Hi

Does anybody know how to find previous Monday date from speficied date.
For example myDate = '22-09-2004' and the previous Sunday should be
'13-09-2004'

TIA
Gabriel

 

[Nikos Yannacopoulos]

Gabriel,

It's very easy to do with a user defined function in VBA, only you're not
clear in whether you are looking for the last Monday or Sunday! Anyway, the
idea is:

Function Last_Monday_Before(vDate As Date)
If Weekday(vDate, vbSunday) = 2 Then
Last_Monday_Before = vDate - 7
Else
Last_Monday_Before = vDate - Weekday(vDate, vbSunday) + 2
End If
End Function

Or

Function Last_Sunday_Fefore(vDate As Date)
If Weekday(vDate, vbSunday) = 1 Then
Last_Sunday_Fefore = vDate - 7
Else
Last_Sunday_Fefore = vDate - Weekday(vDate, vbSunday) + 1
End If
End Function

Paste the code in a general module and save. Then you can use the function
like, for instance:

Last_Monday_Before([SomeDateFieldOrControl])

or

Last_Monday_Before(#24/09/2004#)

in any query, form, report, code procedure or macro action argument, and it
wil return the calculated date based on the parameter in the parantheses.

HTH,
Nikos

Last