Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
I have a small sheet of allocated work / cases to be seen, in column
C cells would like the WEEK number to be automatically entered when the allocation date is entered into column D cells. Or the other way round e.g. I dont mind making column C the date then column D the week number. EG WEEK Allocated A B C D X X 24 17/6/11 thanks John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
I assume your question is not really how to write an "If" function but how to determine the week
number... http://www.cpearson.com/Excel/WeekNumbers.aspx -- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (Extras for Excel add-in: convenience built-in) "Johnnyboy5" wrote in message ... I have a small sheet of allocated work / cases to be seen, in column C cells would like the WEEK number to be automatically entered when the allocation date is entered into column D cells. Or the other way round e.g. I dont mind making column C the date then column D the week number. EG WEEK Allocated A B C D X X 24 17/6/11 thanks John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
On Jun 18, 11:19*am, "Jim Cone" wrote:
I assume your question is not really how to write an "If" function but how to determine the week number...http://www.cpearson.com/Excel/WeekNumbers.aspx -- Jim Cone Portland, Oregon USA *.http://www.mediafire.com/PrimitiveSoftware*. (Extras for Excel add-in: *convenience built-in) "Johnnyboy5" wrote in ... I have a small sheet of allocated work / cases to be seen, *in column C cells would like the WEEK number to be automatically entered when the allocation date is entered into column D cells. *Or the other way round e.g. * I dont mind making column C the date then column D the week number. EG * * * * * * * * WEEK * * * *Allocated A * * B * * * * * C * * * * * * * D X * * *X * * * * *24 * * * * * *17/6/11 thanks John Yes your right - however if I use the first formula today's date 18/6/11 says its week 25 when its still week 24 20/6/11 (Monday) would be the start of week 25 Wow ! I like the first one "Week Number Absolute" but how could it be written to have Monday being the first day of the week ? The others are a bit complex for me ! thanks John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
On Jun 18, 2:02*pm, Johnnyboy5 wrote:
On Jun 18, 11:19*am, "Jim Cone" wrote: I assume your question is not really how to write an "If" function but how to determine the week number...http://www.cpearson.com/Excel/WeekNumbers.aspx -- Jim Cone Portland, Oregon USA *.http://www.mediafire.com/PrimitiveSoftware*. (Extras for Excel add-in: *convenience built-in) "Johnnyboy5" wrote in ... I have a small sheet of allocated work / cases to be seen, *in column C cells would like the WEEK number to be automatically entered when the allocation date is entered into column D cells. *Or the other way round e.g. * I dont mind making column C the date then column D the week number. EG * * * * * * * * WEEK * * * *Allocated A * * B * * * * * C * * * * * * * D X * * *X * * * * *24 * * * * * *17/6/11 thanks John Yes your right *- however if I use the first formula today's date 18/6/11 says its week 25 when its still week 24 *20/6/11 (Monday) would be the start of week 25 Wow ! I like the first one "Week Number Absolute" but how could it be written to have Monday being the first day of the week ? The others are a bit complex for me ! thanks John Hang on...I think I have got it now...using "Week Number From Date" with a few $ signs added thanks - job done John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
Hi John
Reading from Chips site, I think this is what you're looking for... ISO Week Number Public Function IsoWeekNumber(InDate As Date) As Long IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays) End Function Read carefully through the explaination and it will become clear...This method uses the International Standards Organization (ISO) definition of a week number.An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday.Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.Note that it is possible that the first few days of a year may fall into week 52 or week 53.Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday,and second, that the first week must contain a Thursday.For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010),the week doesn't contains Thursday (or, said another way,the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010.The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009.Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010,which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).HTHMick.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week numbers from an entered date
On Jun 18, 2:43*pm, "Vacuum Sealed" wrote:
Hi John Reading from Chips site, I think this is what you're looking for... ISO Week Number Public Function IsoWeekNumber(InDate As Date) As Long * * IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays) End Function Read carefully through the explaination and it will become clear...This method uses the International Standards Organization (ISO) definition of a week number.An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday.Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.Note that it is possible that the first few days of a year may fall into week 52 or week 53.Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday,and second, that the first week must contain a Thursday.For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010),the week doesn't contains Thursday (or, said another way,the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010.The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009.Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010,which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).HTHMick.... Thanks - I will also give that a try out. Johnnboy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert regular Date to Week Ending or Week Beginning Dates | Excel Discussion (Misc queries) | |||
how do i stop excel from converting numbers entered into a date | Excel Discussion (Misc queries) | |||
generate serial numbers and date it entered | Excel Discussion (Misc queries) | |||
How do I get a cell to show the day of the week when date entered | Excel Discussion (Misc queries) | |||
Transpose week numbers to date | Excel Discussion (Misc queries) |