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
|