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

--
Message posted from http://www.ExcelForum.com

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).
-----Original Message-----
I have adapted some code from Tom Ogilvy that is processed
at Worksheet
Open. It gives me the weekend date "dWeekEnding".(Which for my
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 Monday
- 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 the
code above) and then to be available in any Procedure in my
application. (I have tried making dWeekEnding "Public" but
just got an
error about a mismatch).

Please can someone suggest a way to do this.

Many thanks.


---
Message posted from http://www.ExcelForum.com/

.

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com