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

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!!

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

"msbutton27" wrote:

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!!

=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


"msbutton27" wrote in message
...
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

"msbutton27" wrote:

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!!

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)


"msbutton27" wrote in message
...
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

"msbutton27" wrote:

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!!

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" wrote:

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

"msbutton27" wrote:

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!!

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


"Bob Phillips" wrote:

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)


"msbutton27" wrote in message
...
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

"msbutton27" wrote:

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!!

On Wed, 25 May 2005 12:47:02 -0700, "msbutton27"
wrote:

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 structu

=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

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)


"msbutton27" wrote in message
...
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


"Bob Phillips" wrote:

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)


"msbutton27" wrote in message
...
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

"msbutton27" wrote:

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!!