Finding a date

[camlad]

How do I identify a particular day in the year?



Having entered 1/1/2010 into A1 and dragged down the whole year I need a
macro which will find the dates on which a number of events occur and paste
into the adjacent cells in column B the name of the event.



So, how can I find the 3rd Wednesday in April and paste in "Event 1".



There is a large number of events to enter each year so I have in mind a
variable for each event to indicate the weekday, week and month. In this
case the macros might be:



Sub Event01()

iMeet = 3404 '3 = third week, 4 = week day 4, 04 = April

sEvent = "Event 1"

FindDate

End Sub



Sub FindDate()



'break iMeet into week, weekday and month, find date and enter event



End Sub



Thanks



Camlad

 

[Patrick Molloy]

Interesting. you'd need to parse the iMeet value to first get the month, then
find the week and finally get the day

How would yuo define week 1. If 1st is a Tuesday, is Sunday 6th the first
day of week #2 then?

 

[joel]

Did you want the 3 Wednesday of the month? I you consider the 1st da
of the week Sunday, and didn't know what you meant by Week 3 (could o
been interpreted a number of different ways).

Sub Event01()
Dim imeet As Integer
imeet = 3404 '3 = third week, 4 = week day 4, 04 = April

sEvent = "Event 1"

Set cell = FindDate(imeet)

End Sub



Function FindDate(imeet As Integer) As Range

Dim FindDateStr As String

'break iMeet into week, weekday and month, find date and enter event
Iweek = Val(Left(imeet, 1))
IWeekDay = Val(Mid(imeet, 2, 1))
IMonth = Val(Right(imeet, 2))

FirstofMonth = DateSerial(2010, IMonth, 1)
'Assume 1 = sunday for week day
FirstDay = Weekday(FirstofMonth)
'Get first X day of month where X is the week day in IMeet
FirstWeekDay = 1 + (((IWeekDay - FirstDay) + 7) Mod 7)
'find date by adding the number of week to the firstWeekDay results
FindDay = FirstWeekDay + ((Iweek - 1) * 7)
'find date is the serial date to search
FindDateX = FirstofMonth + FindDay - 1
'get string version of date
FindDateStr = Format(FindDateX, "M/D/YYYY")
Set FindDate = Columns("A").Find(what:=FindDateStr, _
LookIn:=xlValues, lookat:=xlWhole)


End Functio

 

[Ron Rosenfeld]

So, how can I find the 3rd Wednesday in April

Addressing this part of your question, the general formula:

======================
Function NthWD(d As Date, DOW As Long, WeekNum As Long) As Date
'DOW = Day Of Week
'Weeknum assumes week starts on DOW
NthWD = d - Day(d) + 1 + 7 * WeekNum - Weekday(d - Day(d) + 8 - DOW)
End Function
====================
--ron

 

[camlad]

Many thanks Patrick, joel and Ron - food for thought.
Showing my ignorance, there is one other thing which will help me just now.
I have 1/1/2001 in A1, formatted 'dddd d mmm yyyy'. I need to know what day
it is, ie 40179, but do not know how to find that other than starting off
with a macro like this - there must be a better way than this crude
recording.
Camlad

Sub Macro6()
'Range("A:A") is formatted 'dddd d mmm yyyy'
Range("A1").Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B1").Select
Application.CutCopyMode = False
Selection.NumberFormat = "0"
iYearstart = Range("B1")
Range("A5") = iYearstart + 3
End Sub

 

[joel]

Dates start at Jan 1 , 1900 and add one for each day. The number 4017
is Jan 1, 2010. If you enter in a worksheet 40179 and then format th
cell to a date format you wil see Jan 1, 2010. If you enter in a cel
1/1/2010 and then format the cell as a number you will see 40179. th
hourt and minutes are the fractional part of the date number.

to get the date in VBA simply do this

Dim Mydate as integer
MyDate = int(Range("A1").value

 

[Ron Rosenfeld]

Many thanks Patrick, joel and Ron - food for thought.
Showing my ignorance, there is one other thing which will help me just now.
I have 1/1/2001 in A1, formatted 'dddd d mmm yyyy'. I need to know what day
it is, ie 40179, but do not know how to find that other than starting off
with a macro like this - there must be a better way than this crude
recording.
Camlad

range("A1").value2

will return the unformatted value stored in A1.

So, something like

dim d as double
d = [A1].value2

Or, if you only want the date portion:

dim d as long
d = int([a1].value2)

--ron

 

[joel]

ron: Why would you want to declare an integer data as a double which i
used for Factional numbers? I could see it being a long, but no
double. I wouldn't even use double for time (hours, minutes, seconds
because the standard excel time is only stored as single precision

 

[camlad]

Thanks to you all - that's what I want - I'll leave the 'double' discussion
to the experts.

Camlad

 

[Ron Rosenfeld]

ron: Why would you want to declare an integer data as a double which is
used for Factional numbers?

You raise a few issues.

With regard to using Long vs Integer in VBA, I have been using Long in place of
Integer since I became aware of a posting at
http://msdn.microsoft.com/en-us/library/aa164754(office.10).aspx

where it states "...VBA converts all integer values to type Long, even if they
are declared as type Integer. Therefore, there is no longer a performance
advantage to using Integer variables; in fact, Long variables might be slightly
faster because VBA does not have to convert them."

With regard to using Double, I thought I had implied that I was returning Time
as well as the date.

I wouldn't even use double for time (hours, minutes, seconds)
because the standard excel time is only stored as single precision.

I don't believe that is true. The Single data type can only express up to about
7 decimal digits. Excel can store date/time strings up to 1/1000 of a second
which would require more precision than that. For current dates, you'd need
five digits before the decimal and ten after, to get to 1/1000 second
precision.

It is certainly not true for VBA where it is explicitly stated that Date
variables are stored as IEEE 64-bit floating-point numbers (Singles are only
32-bit). I've not found such an explicit statement for Excel, but you'd need
more than 7 digit precision to store dates to 0.001 seconds.



--ron

 

[joel]

I not sure but a couple of months ago a found that time was only givin
single precision results. Maybe the worksheet only gives singl
precision.

what I was doing was to put 00:00 in cell A1 and 00:01 in A2 and the
used auto fill to get 24 hours. I then was trying to lookup up result
and found interestting results as you would expect. I couldn't ge
certain hours to match the VBA code because of the fractional amoun
weren't equal to the 12nth decimal place. I tried declaring the VB
variables as both single and double and found the single precision gav
better results (not perfect). It appear that excel wasn't handling th
last carry bit properly (or consistently)inside the micro-processo
chip. But it still appeared that excel that the VBA code was onl
giving single precision accuarcy

 

[Ron Rosenfeld]

I not sure but a couple of months ago a found that time was only giving
single precision results. Maybe the worksheet only gives single
precision.

what I was doing was to put 00:00 in cell A1 and 00:01 in A2 and then
used auto fill to get 24 hours. I then was trying to lookup up results
and found interestting results as you would expect. I couldn't get
certain hours to match the VBA code because of the fractional amount
weren't equal to the 12nth decimal place. I tried declaring the VBA
variables as both single and double and found the single precision gave
better results (not perfect). It appear that excel wasn't handling the
last carry bit properly (or consistently)inside the micro-processor
chip. But it still appeared that excel that the VBA code was only
giving single precision accuarcy.

What does that mean "time was only giving single precision results"?

Since single precision is limited to about 7 digits, and current dates require
five digits to the left of the decimal to represent the date, that leaves only
two digits to represent the time.

So that would mean that if Excel were using single precision values to store
times on the worksheet, there would be no way to represent increments of less
than 14.4 seconds! That is clearly not the case. Excel can represent times
that differ by 0.001 seconds.

You were probably not handling rounding errors appropriately to cause the error
you are describing. There is a lot of information on the web and in the MSKB
about this.

There is certainly no question but that you can enter data in Excel to 1/1000's
of a second, and have it displayed appropriately.

For example:

39814.3333333448
39814.3333333565

are clearly NOT values which can be expressed in single precision.

And with a custom format of:

dd mmm yyyy hh:mm:ss.000

they display as:

01 Jan 2009 08:00:00.001
01 Jan 2009 08:00:00.002

The single precision equivalents of those values would be:

39814.33

and, displayed in the same format would be:

01 Jan 2009 07:55:12.000

In other words, single precision cannot even display to ONE MINUTE accurately,
much less 1/1000 of a second.
--ron

 

[joel]

I'm still not convinced you are correct. if there are 86400 seconds i
a day and we arre dealing with single precision of 2^24 then th
resolution would be

86400/2^24 = 0.00514984130859375. The accuracy is only .005 and th
other digits are only conversion errors not real accuracy

 

[Ron Rosenfeld]

I'm still not convinced you are correct. if there are 86400 seconds in
a day and we arre dealing with single precision of 2^24 then the
resolution would be

86400/2^24 = 0.00514984130859375. The accuracy is only .005 and the
other digits are only conversion errors not real accuracy.

There are two issues I think you are overlooking.

1. Excel can accurately represent time to 0.001 seconds. 0.001 seconds =
1/86400/1000 = 0.000000011574074

2. Using single precision, you can only have SEVEN digits TOTAL. If you have
even ONE digit to the left of the decimal, that leaves you six digits to the
right of the decimal. So you could express 1.000001 but not 1.000000011574074

--ron

 

[joel]

That is my point. When I did the autofill the time appeared to b
acurate to more than 7 places even though it was only 7 places and th
additional least significant places where just conversion errors.
Delcaring a variable as a double did not give any better accuracy, i
just extended the conversion errors

 

[Ron Rosenfeld]

That is my point. When I did the autofill the time appeared to be
acurate to more than 7 places even though it was only 7 places and the
additional least significant places where just conversion errors.
Delcaring a variable as a double did not give any better accuracy, it
just extended the conversion errors.

What do you mean by "7 places"?

Even a time expressed as hh:mm:ss requires considerably more than "7 places".

There's something else going on. You haven't provided enough information to be
sure, but it certainly IS the case that Excel can store and express time to
more than what would be allowed by 7 decimal digits.
--ron