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?

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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