Finding a date from year, week of the year (1-52) and day of the week (1-7)

  • Thread starter Thread starter Loucas
  • Start date Start date
L

Loucas

Hello,

This is my first posting.

I am trying to obtain a date from the following information:
- Day of the week (i.e. 1 to 7, Mon to Sun),
- Week of the year (i.e. 1 to 52), and
- Year.

Example:

Today is the 3rd day of the 5th week of 2004. Can I use the numbers 3,
5 and 2004 to obtain the exact date (which is 28/01/2004)?

Is there a function or an easy way to calculate the exact date?

Many thanks

Loucas
 
Loucas,

Not tested extensively, but this works for the data supplied

=DATE(2004,1,(5-1)*7+3)-(WEEKDAY(DATE(2004,1,1)-1,2))

This assumes weeks starting on Mon for the WEEKDAY function, as in your
example.

--

HTH

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

First posting! I'm worried about further ones!!!

There's problems with your formula. From the manual answer of 3rd of
5th of 2004, OP is using Week 1 starts 1-Jan and week 2 starts the
following Monday. So week 1 has only got 4 days in it this year. I
also get problems at the end of the year. 6th of 53rd 2004 returns
1-Jan-2005.

Here's my, very clumsy formula that is still not fully tested but
seems OK so far.

A1 is Day, B1 is ordinal week, C1 is Year:
=IF(B1=1,IF(A1>WEEKDAY(DATE(C1,1,1),2),NA(),DATE(C1-1,12,31)+A1),IF((B
1-1)*7+A1-(7-WEEKDAY(DATE(C1,1,1),2))>DATE(C1,12,31)-DATE(C1-1,12,31),
NA(),DATE(C1,1,(B1-1)*7+A1-(7-WEEKDAY(DATE(C1,1,1),2)))))

I default to NA if user puts in a day number greater than the number
of days in week 1. I also default to NA if calculated date is in the
next year.

As I say not fully tested and I'm sure that a more efficient approach
exists.

--
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 said:
Hi Bob and Loucas!

First posting! I'm worried about further ones!!!

There's problems with your formula. From the manual answer of 3rd of
5th of 2004, OP is using Week 1 starts 1-Jan and week 2 starts the
Click to expand...

or perhaps week1 starts 29th Dec 2003?
 
Hi Bob!

Yes!

Hi Loucas!

Can you tell us what algorithm you're using for week numbering?

Four common bases:

ISO8601:2000 Day 1 of Week 1 is the Monday of the week with Jan-4 in
it. Thereafter, it's a 7 day gap. This ensures that all weeks have 7
days. However, as with this year, Week 1 can start before the
beginning of the calendar year.

WEEKNUM Option 1. Day 1 Week 1 is Jan-1. Week 2 starts the following
Sunday.

WEEKNUM Option 2. Day 1 Week 1 is Jan-1. Week 2 starts the following
Monday

Simple week numbering. Day 1 Week 1 is Jan-1, Day 1 of week 2 is
8-Jan.



--
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.
 
Hi Bob!

Using the ISO basis is a lot easier subject to use of John Green's
ISOYEARSTART function:

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

This gives a formula for day, weeknumber, year:

=IF((YEAR(isoyearstart(C1)+(B1-1)*7+A1-1))>=YEAR(isoyearstart(C1+1)),N
A(),isoyearstart(C1)+(B1-1)*7+A1-1)

All that has to be trapped is the case where the calculated date is
beyond day 1 of week 1 of the following year.

--
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.
 
Hi Norman,

For ISO based weeknumbering,
Assuming DoW: 1 to 7 (Sun to Sat)
Yr: the year number (ex: 2004)
WN: the week number (1 to ...)

=7*WN+DATE(Yr,1,3)-WEEKDAY(DATE(Yr,1,3))-6+DoW

If you'd like to trap for bad input, here's one that deals with proper WN and
DoW. But where do we draw the line here? As user can input too low year, aka
1888, or text, etc.

=IF(OR(WN<1,WN>52+AND(WEEKDAY(DATE(A4,{2;8},))>5),DoW<1,DoW>7),
NA(),7*WN+DATE(Yr,1,3)-WEEKDAY(DATE(Yr,1,3))-6+DoW)

Regards,

Daniel M.
 
Oops.
In the latter formula, replace the A4 with Yr, giving:

=IF(OR(WN<1,WN>52+AND(WEEKDAY(DATE(Yr,{2;8},))>5),DoW<1,DoW>7),
NA(),7*WN+DATE(Yr,1,3)-WEEKDAY(DATE(Yr,1,3))-6+DoW)

Regards,

Daniel M.
 
Back
Top