Get Date today from WeekEnding

  • Thread starter Thread starter Jako
  • Start date Start date
J

Jako

I have adapted some code from Tom Ogilvy that is processed at Workshee
Open. It gives me the weekend date "dWeekEnding".(Which for m
application is a Friday).

Dim dt As Date
Dim dWeekEnding As Variant

If Weekday(Date, vbSunday) = 2 Then
dt = Date + 4
dWeekEnding = Format(dt, "dd.mm.yyyy")
End If

What i want to do (But can't) is find a way to get the dates for Monda
- Friday into variables (With the format "dd.mm.yyyy"):

Day1Date(Mondays Date)
Day2Date(Tuesdays Date)
Day3Date(Wednesdays Date)
Day4Date(Thursdays Date)
Day5Date(FridaysDate)

So if dWeekEnding = "23.07.2004" i want
Day1Date ="19.07.2004"
Day2Date ="20.07.2004" etc.

I want them to be defined at worksheet open (So incorparated into th
code above) and then to be available in any Procedure in m
application. (I have tried making dWeekEnding "Public" but just got a
error about a mismatch).

Please can someone suggest a way to do this.

Many thanks
 
I don't fully understand what you are asking. You CAN
format a date as "dd.mm.yy" by just specifying it that way
in a Text function (formula) or in the FORMAT function (macro).
 
Thanks for the reply but the format is not what i'm stuck with.

I am stuck with not being able to programmatically allocate the date
before the WeekEnd date.

I know the WeekEnd Date because of the code i have posted.
What i can't do is make the data global to my application.
I also cannot fathom out, given that Friday will always be the Date fo
the WeeEndingDate, how to put the Monday-Friday dates into Globall
recognised variables. I know i already have the Friday date but i stil
can't make this Globally recognised.

Thanks agai
 
I managed to sort it out myself.

I removed the code from the "Worksheet Open" Activate event and move
it into a Module and made a call to the Module on opening th
worksheet.

Used this code:

Dim dt As Date

' If day = Monday
If Weekday(Date, vbSunday) = 2 Then
dt = Date + 4
dWeekEnding = Format(dt, "dd.mm.yyyy")
End If
Day1Date = Format(dt - 4, "dd.mm.yyyy")
Day2Date = Format(dt - 3, "dd.mm.yyyy")
Day3Date = Format(dt - 2, "dd.mm.yyyy")
Day4Date = Format(dt - 1, "dd.mm.yyyy")
Day5Date = dWeekEnding

Not pretty and probably bad programming coz i guess i could have used
For - Next loop, but still, it does the job
 
Back
Top