Week Number and Year Format

[virtualfunker]

Can I format a cell to give week number and year format i.e. 1-March-04 is
1004 (that week being week 10)

Thanks

 

[Andy Wiggins]

This page might be a help:
http://www.bygsoftware.com/examples/iso_dates.htm
This page is based on ISO 8601.

It includes the Excel formula and an online week-number calculator using
Microsoft's spreadsheet component.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Bob Phillips]

You can get it if you put it in a separate cell

=WEEKNUM(A1)&TEXT(A1,"yy")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Virtualfunker!

It depends on your weeknumber system:

From weeknumber for March-1 is 10 I deduce it is:

=WEEKNUM(A1,1)&TEXT(A1,"yy") [Day 1 week 1 is 1-Jan, Day 2 Week 2 is
following Sunday]
OR:
=WEEKNUM(A1,2)&TEXT(A1,"yy") [Day 1 of week 1 is 1-Jan, Day 2 of Week
2 is following Monday]
OR:
=ISOWEEKNUM(A1)&TEXT(A1,"yy") [Day 1 of week 1 is Monday of week with
Jan 4 in it, Day 1 week 2 is following Monday]


Here's the UDF for ISOWEEKNUM:

Function ISOWEEKNUM(d1 As Date)
'Laurent Longre function
Dim Test As Long
Test = DateSerial(Year(d1), Month(d1), Day(d1))
If Test <= 0 Or Test > 401769 Then
ISOWEEKNUM = CVErr(xlErrNA)
Exit Function
End If
Dim d2 As Long
d2 = DateSerial(Year(d1 - WeekDay(d1 - 1) + 4), 1, 3)
ISOWEEKNUM = Int((d1 - d2 + WeekDay(d2) + 5) / 7)
End Function

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.