Work Week to Date?

[Guest]

Hi

I'm looking for a script which will map a work week onto the date of the Friday of that week. Does anyone know if such a script already exists

TI

Dave

 

[Norman Harker]

Hi David!

First define your week numbering system.

See:
http://www.cpearson.com/excel/weeknum.htm

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

 

[Guest]

It basically seems to be the ISO week numbering system (WWs are Sun-Sun - WW1 of 2004 actually starts on 28 Dec 2003).

However, that system can have 53 WW in a year, and a quick check reveals that 26-31 of Dec 2004 actually falls in WW1 of 2005 using our system

Also, that script converts Dates -> WWs... I need to go the opposite direction

TIA again

Dave

 

[Norman Harker]

Hi David!

Now we have the system we can start to answer the question.

I gather that with a given week number of a given year you want the
Friday of that week.

For this a User Defined Function by John Green is very helpful:

Function ISOYEARSTART(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7
If WeekDay < 4 Then
ISOYEARSTART = NewYear - WeekDay
Else
ISOYEARSTART = NewYear - WeekDay + 7
End If
End Function 'ISOYEARSTART

With this:
A1: 2004
B1: 25

C1:
=ISOYEARSTART(A1)+(B1-1)*7-1+5
Returns: Fri, 18-Jun-2004

D1; [Check using an ISOWEEKNUM UDF]
=ISOWEEKNUM(C1)
Returns: 25

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

 

[Norman Harker]

Hi David!

If you don't want a UDF approach, here's a formula that will do it:

=7*B1+DATE(A1,1,3)-WEEKDAY(DATE(A1,1,3))-5+4

With the exception of the +4 added to the end, it's the formula for
finding the Monday of the weeknumber in B1 for the year in A1.

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

 

[Guest]

Cheers... that did the trick with a bit of preprocessing of my data.

 

[Guest]

Thanks, Norman

With a bit of preprocessing of my data, that did the trick.

 

[Norman Harker]

Hi David!

Thanks for thanks and confirmations help Google searchers.

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