=A2-(WEEKDAY(A2)-2)

--

HTH

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


"DKY" wrote in message
...

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=40017

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" wrote in message
...

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.


--
DKY
------------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...o&userid=14515
View this thread: http://www.excelforum.com/showthread...hreadid=400172

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" wrote in message
...

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

I finally found something, thanks for the help anyway.

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

--
DK
-----------------------------------------------------------------------
DKY's Profile: http://www.excelforum.com/member.php...fo&userid=1451
View this thread: http://www.excelforum.com/showthread.php?threadid=40017

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

--

HTH

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


"DKY" wrote in message
...

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