Thread: dates
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Incidental Incidental is offline
external usenet poster
 
Posts: 226
Default 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