How do I get excel to recognize the date of this weeks Monday? Fo
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
make that for this weeks Monday no matter what day of the week it is.
Monday, Tuesday, Wednesday, Thursday or Friday?

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

=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