Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
Hi,
Is it possible to check the a specific date such as 9th of every month? Any ideas is appreciated. Tks. rgds, newbie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linking computer dates (time) to spreadsheet dates that have formu | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Toggle a range of Julian dates to Gregorian Dates and Back | Excel Programming | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) |