How can I convert a date into a week number...

[msbutton27]

Hello,

I am trying to determine if it is possible to convert a 6 digit number into
a week - for example:
050525 = yymmdd = 2005, May 25th
and I want my field to determine what the week number of the year is...

This week is week 21, therefore based on my entry of the date format above I
want the week column autofilled...

Is this possible?

Thanks - Have a Good One!!

 

[msbutton27]

I also forgot to mention that the nubmer in my first column (the date) is
actually part of a bigger number, therefore I want to know how I can parse
the data...ie:

050523-23432
I only need
050523

....Mike

 

[N Harkawat]

=WEEKNUM(DATE((LEFT(A1,2)+2000),(MID(A1,3,2)),(MID(A1,5,2))))

you will need to install Analysis took pak add-in to use the weeknum
function
and also assumed that 05 in the first part of the year is always 2005 and
not 1995

Also 2005, May 25th gives me week 22 and NOT week 21

 

[Bob Phillips]

This looks like an ISO weeknum you are wanting, so I would do it in 2 steps

In B1, add this formula

=DATE(2000+MID(A1,5,2,MID(A1,1,2)),MID(A1,3,2))

and then in C1, add

=1+INT((--B1-DATE(YEAR(B1+4-WEEKDAY(B1+6)),1,5)+WEEKDAY(DATE(YEAR(B1+4-WEEKD
AY(B1+6)),1,3)))/7)

BTW I am assuming that those dates are US format, if not in B1 use

=DATE(2000+MID(A1,5,2),MID(A1,3,2),MID(A1,1,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

Here is a formula from Daniel Maher, with the date in A1

=1+INT(MIN(MOD(A1-DATE(YEAR(A1)+{-1;0;1},1,5)+WEEKDAY(DATE(YEAR(A1)+{-1;0;1},1,3)),734))/7)

Daniel also wrote an ISO week function

Public Function IsoWeekNum(d1 As Date) As Integer
' Daniel Maher
Dim d2 As Long
d2 = DateSerial(Year(d1 - Weekday(d1 - 1) + 4), 1, 3)
IsoWeekNum = Int((d1 - d2 + Weekday(d2) + 5) / 7)
End Function


Press Alt + F11, click insert module and paste in the code, press alt + Q to
close the VBE and then use as

=IsoWeekNum(A1)


Regards,

Peo Sjoblom

 

[msbutton27]

Bob - do i need to install any addon's for your suggestion - I am unable to
enter your first B1 Date command, complains that there are two many comands
for this function...

....Mike

 

[Ron Rosenfeld]

I also forgot to mention that the nubmer in my first column (the date) is
actually part of a bigger number, therefore I want to know how I can parse
the data...ie:

050523-23432
I only need
050523

Well, to parse out the date, assuming the above structure:

=DATE(LEFT(A1,2)+1900+100*(--LEFT(A1,2)<29),MID(A1,3,2),MID(A1,5,2))

To determine the weeknumber depends on your definition.

There are the various Excel definitions -- see the WEEKNUM worksheet function.

However, there is also an ISO standard which is not supported by the Excel
WEEKNUM worksheet function but can be computed using this UDF:

===========================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum > 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
============================

To enter this UDF, <alt+F11> opens the VB Editor.
Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the above code into the window that opens.

To use this function:

=ISOWeeknum(your_date)

Putting it all together, with your string in A1, the ISO weeknumber can be
computed using the formula:

=isoweeknum(DATE(LEFT(A1,2)+1900+100*(
--LEFT(A1,2)<29),MID(A1,3,2),MID(A1,5,2)))

Be sure to format the cell as General (or Number)


--ron

 

[Bob Phillips]

Mike,

Sorry I hade to change before posting as I have UK dates, and a ) got out of
place. Try

=DATE(2000+MID(A1,5,2),MID(A1,1,2),MID(A1,3,2))

--

HTH

RP
(remove nothere from the email address if mailing direct)