Week numbers from an entered date

  • Thread starter Thread starter Johnnyboy5
  • Start date Start date
J

Johnnyboy5

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
 
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" <[email protected]>
wrote in message
Click to expand...

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
 
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
Click to expand...

Hang on...I think I have got it now...using "Week Number From Date"
with a few $ signs added

thanks - job done

John
 
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....
 
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....
Click to expand...

Thanks - I will also give that a try out.

Johnnboy
 
Back
Top