I was wondering how I can get VBA in Excel'03 to recognize that if today is
Tuesday the 17th, or Firday the 20th, then the previous Monday must have been
the 16th. Or is it is Friday April the 3rd then the previous Monday was
March 30th? Escentially I am just looking for a way to have the VB identify
what Day today is as well as the date and then calculate what the previous
Monday was... Unless the current day happens to actually be Monday, in which
case it just uses that date.

Dim tDate, mDate As Date

tDate = Format(Now(),"m/d/yyyy")
tDay = Day(tDate)

If tDay <> "Monday" Then mDate = tDate - tDay

The above code it what I toyed with but it has failed at every itteration I
made of it.


Thanks In Advance!

Rob,

Try this

tDate = Date - WeekDay(Date, 2) + 1

For future work you simply change the last 1 to a 2 for Tuesday etc.

Mike

"Rob" wrote:

> I was wondering how I can get VBA in Excel'03 to recognize that if today is
> Tuesday the 17th, or Firday the 20th, then the previous Monday must have been
> the 16th. Or is it is Friday April the 3rd then the previous Monday was
> March 30th? Escentially I am just looking for a way to have the VB identify
> what Day today is as well as the date and then calculate what the previous
> Monday was... Unless the current day happens to actually be Monday, in which
> case it just uses that date.
>
> Dim tDate, mDate As Date
>
> tDate = Format(Now(),"m/d/yyyy")
> tDay = Day(tDate)
>
> If tDay <> "Monday" Then mDate = tDate - tDay
>
> The above code it what I toyed with but it has failed at every itteration I
> made of it.
>
>
> Thanks In Advance!

And including your formatting

tDate = Format(Date - WeekDay(Date, 2) + 1, "mm/dd/yyyy")

Mike

"Mike H" wrote:

> Rob,
>
> Try this
>
> tDate = Date - WeekDay(Date, 2) + 1
>
> For future work you simply change the last 1 to a 2 for Tuesday etc.
>
> Mike
>
> "Rob" wrote:
>
> > I was wondering how I can get VBA in Excel'03 to recognize that if today is
> > Tuesday the 17th, or Firday the 20th, then the previous Monday must have been
> > the 16th. Or is it is Friday April the 3rd then the previous Monday was
> > March 30th? Escentially I am just looking for a way to have the VB identify
> > what Day today is as well as the date and then calculate what the previous
> > Monday was... Unless the current day happens to actually be Monday, in which
> > case it just uses that date.
> >
> > Dim tDate, mDate As Date
> >
> > tDate = Format(Now(),"m/d/yyyy")
> > tDay = Day(tDate)
> >
> > If tDay <> "Monday" Then mDate = tDate - tDay
> >
> > The above code it what I toyed with but it has failed at every itteration I
> > made of it.
> >
> >
> > Thanks In Advance!

Dear Rob, please try this

If this helps click Yes
---------------
Jacob Skaria

Dim varDate,monDate
varDate = Date
monDate = Format(varDate - (IIf(Weekday(varDate) >= vbMonday,
Weekday(varDate) - vbMonday, 7 - (vbMonday - Weekday(varDate)))))

That's a Beautiful solution!!! Thanks Greatly!!



"Mike H" wrote:

> Rob,
>
> Try this
>
> tDate = Date - WeekDay(Date, 2) + 1
>
> For future work you simply change the last 1 to a 2 for Tuesday etc.
>
> Mike
>
> "Rob" wrote:
>
> > I was wondering how I can get VBA in Excel'03 to recognize that if today is
> > Tuesday the 17th, or Firday the 20th, then the previous Monday must have been
> > the 16th. Or is it is Friday April the 3rd then the previous Monday was
> > March 30th? Escentially I am just looking for a way to have the VB identify
> > what Day today is as well as the date and then calculate what the previous
> > Monday was... Unless the current day happens to actually be Monday, in which
> > case it just uses that date.
> >
> > Dim tDate, mDate As Date
> >
> > tDate = Format(Now(),"m/d/yyyy")
> > tDay = Day(tDate)
> >
> > If tDay <> "Monday" Then mDate = tDate - tDay
> >
> > The above code it what I toyed with but it has failed at every itteration I
> > made of it.
> >
> >
> > Thanks In Advance!