How to find previous Monday (date) from specific date

G

Gabriel

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
 
N

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
 
G

Gabriel

Hi Nikos,

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

Gabriel

Nikos Yannacopoulos said:
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
Gabriel said:
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
Click to expand...
Click to expand...
 
N

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
 
Top