ExcelBanter - dates

Hi newbie

i posted this a while ago it might help you out if you change around
to suit your needs

hope it helps

S

Hi

You could try something like the code below which should do what you
want. It will pass the current date to a string variable then split
that string into the following sections

MyDay = the numeric value of the day i.e. the 5th
DayName = the three letter day abbreviation

It will then check MyDay every time the workbook is opened if the
value returned is
5 then the 5th is a weekday presuming you will only be opening it
during the five day working week
6 then the 5th may have been a Sunday so check DayName if it is "Mon"
then the 5th was a Sunday run the code if DayName is not "Mon" do
nothing
7 then the 5th may have been a Saturday so check DayName if it is
"Mon" then the 5th was a Saturday run the code if DayName is not
"Mon"
do nothing

In the workbook module
Option Explicit
Dim MyDate, DayName As String 'Declare your variables
Dim MyDay As Integer

Private Sub Workbook_Open()

MyDate = Format(Date, "DDD DD/MMM/YYYY") 'Pass the full date to a
string

'MyDate = "Sat 06/04/2007" 'to test this sub, comment the line above
'and remove the comment mark from before MyDate, test your variations

Sheets(1).Activate 'Select the worksheet you want to work with

[A3].Value = Right(MyDate, 11) 'Put only the DD/MMM/YYYY to cell A3

MyDay = Mid(MyDate, 5, 2) 'Pass the numeric value of the day to an
integer

DayName = Left(MyDate, 3) 'Pass the day name to a string

Select Case MyDay 'Set to check the date

Case "5" '5th falls on a week day
[L9].Value = [L9].Value - [G9].Value

Case "6" '5th falls on a Sunday
If DayName = "Mon" Then
[L9].Value = [L9].Value - [G9].Value
End If

Case "7" '5th falls on a Saturday
If DayName = "Mon" Then
[L9].Value = [L9].Value - [G9].Value
End If

End Select

End Sub

hope this is of some help to you

S