This weeks Monday

How do I get excel to recognize the date of this weeks Monday? For example. I have this bit of code, (Date - 1) for tommorow or (Date - 2) for wednesday to have it come up with August 29th, 2005. How do I make that for this weeks Monday no matter what day of the week it is. Monday, Tuesday, Wednesday, Thursday or Friday??

=A2-(WEEKDAY(A2)-2)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> How do I get excel to recognize the date of this weeks Monday? For
> example. I have this bit of code, (Date - 1) for tommorow or (Date -
> 2) for wednesday to have it come up with August 29th, 2005. How do I
> make that for this weeks Monday no matter what day of the week it is.
> Monday, Tuesday, Wednesday, Thursday or Friday??
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=400172
>

So I could probably do like this then.
If Range("H2") > ((A2-(WEEKDAY(A2)-2)
)-7)
and it will give me something like this?
If Range("H2") > (08/22/2005)

No you cannot use worksheet functions directly in a cell like that. You
either use worksheet functions fully

If Range("H2").Value > Range("A2").Value - _
(WorksheetFunction.Weekday(Range("A2").Value) - 2) Then

or use the VBA function

If Range("H2").Value > Range("A2").Value - _
Weekday(Range("A2").Value - 2) Then
MsgBox "yes"
End If

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> So I could probably do like this then.
> If Range("H2") > ((A2-(WEEKDAY(A2)-2)
> )-7)
> and it will give me something like this?
> If Range("H2") > (08/22/2005)
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=400172
>

Where's A2 coming from? I'm sorry if I wasn't clear earlier but here's what I'm looking for. I currently have this.
If Range("H2") > (Date-1)
what this is going to give me (because its Tuesday) is yesterday's (Monday's) date. Tomorrow I'm going to have to change it to this
If Range("H2") > (Date-2)
to continue to get this week's Monday's date. Then on Thursday I'm going to have to change it to this
If Range("H2") > (Date-3)
to get this week's Monday's date. How do I make it so I don't have to go into the code everytime I want to use the code and adjust my conditional statement depending upon what day it is today. I'm horrible with dates and I want what appears in the parenthesis to be the date of whatever week I use the macro's Monday.

I finally found something, thanks for the help anyway.

GetMondayDate = Date - Weekday(Date, vbMonday) + 1
If Range("H2") >= GetMondayDate

A2 was just an example, a suggested cell for the date to transform to
Monday. If you just want Today, use

If Range("H2").Value > Date - Weekday(Date - 2) Then
MsgBox "yes"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Where's A2 coming from? I'm sorry if I wasn't clear earlier but here's
> what I'm looking for. I currently have this.
> If Range("H2") > (Date-1)
> what this is going to give me (because its Tuesday) is yesterday's
> (Monday's) date. Tomorrow I'm going to have to change it to this
> If Range("H2") > (Date-2)
> to continue to get this week's Monday's date. Then on Thursday I'm
> going to have to change it to this
> If Range("H2") > (Date-3)
> to get this week's Monday's date. How do I make it so I don't have to
> go into the code everytime I want to use the code and adjust my
> conditional statement depending upon what day it is today. I'm
> horrible with dates and I want what appears in the parenthesis to be
> the date of whatever week I use the macro's Monday.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=400172
>

That transforms Sunday to previous Monday, mine takes it to the next.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I finally found something, thanks for the help anyway.
>
> GetMondayDate = Date - Weekday(Date, vbMonday) + 1
> If Range("H2") >= GetMondayDate
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=400172
>