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
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 |
#10
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 |
#11
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 |
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 |