Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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? "camlad" wrote: 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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
Did you want the 3 Wednesday of the month? I you consider the 1st day of the week Sunday, and didn't know what you meant by Week 3 (could of 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 Function -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Mon, 19 Oct 2009 18:46:10 +0100, "camlad" wrote:
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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 "camlad" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
Dates start at Jan 1 , 1900 and add one for each day. The number 40179 is Jan 1, 2010. If you enter in a worksheet 40179 and then format the cell to a date format you wil see Jan 1, 2010. If you enter in a cell 1/1/2010 and then format the cell as a number you will see 40179. the 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) -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Tue, 20 Oct 2009 18:31:23 +0100, "camlad" wrote:
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
ron: Why would you want to declare an integer data as a double which is used for Factional numbers? I could see it being a long, but not double. I wouldn't even use double for time (hours, minutes, seconds) because the standard excel time is only stored as single precision. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
Thanks to you all - that's what I want - I'll leave the 'double' discussion
to the experts. Camlad "camlad" wrote in message ... 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 "camlad" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Tue, 20 Oct 2009 19:58:13 +0100, joel wrote:
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/libr...ice.10%29.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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Wed, 21 Oct 2009 11:11:22 +0100, joel wrote:
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Wed, 21 Oct 2009 13:00:51 +0100, joel wrote:
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
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. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=145688 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a date
On Wed, 21 Oct 2009 18:59:31 +0100, joel wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a Date 90 days previous to present date | Excel Worksheet Functions | |||
FINDING THE DATE 75 DAYS AFTER A SPECIFIED DATE | Excel Worksheet Functions | |||
Finding a date range from given date | Excel Worksheet Functions | |||
finding a date/time in a list that is closest to an existing date/ | Excel Discussion (Misc queries) | |||
Finding the Monday date based on a different date in same week | Excel Worksheet Functions |