Excel: Find a date for a week number 5

[MOUSUSER]

In one cell I want to be able to type a week number for it to return (in another cell) the monday date for that week number. I need to be able to type any week number in at any time for the year and also be able to update it automatically for next year.

 

[xlbo]

Don't ask for much do you ?? ;-)
This should do the trick.....
=VALUE("01/01/"&YEAR(TODAY())) + (A1*7)-8
where the week number is typed into A1
Format as dd/mm/yy
HTH
Geoff

 

[Hasit]

Awesome Geoff. Starworthy.

 

[xlhelp]

I believe that deserves a second star.

All I came up with was if A1 has the week number and B1 has the first of the year, than Monday of the week is

=B1+A1*7-(WEEKDAY(B1+A1*7)+5)

Thanks, Geoff.

Indu

 

[PeterMoran]

Hi Geoff,

Your formula looks great, but when I tried to use it I found it only appears to work correctly for those years which start on a Tuesday - years which start on a different day are out because it always calculates the date from 1/1 by adding n times 7 days and then taking off 8.

You, or rather MOUSUSER, might like to try this from Chip Pearson (

http://www.cpearson.com.excel),

not me:

=DATE(YEAR(TODAY()),1,1+(A1-(2>=WEEKDAY(DATE(YEAR(TODAY()),1,1))))*7)+2-WEEKDAY(DATE(YEAR(TODAY()),1,1))

I should also add that this formula assumes that the first Monday is in the current year.

Good Luck!

 

[MOUSUSER]

Many thanks to you all for your info and I've spent a lot of time using these for dates over the next ten years.

Geoff - I've found the same problem that it works for years starting on a Tuesday. Could always fiddle the end number to compensate I suppose.

xlbo - your solution is really simple and seems to work well.

Peter - the solution was fine but always seemed to be a week out for some bizarre reason so I may go onto the website and have a look.

Again thanks to you all for your help.

 

[xlbo]

Hokey Cokey, try this'n
=VALUE("01/01/"&YEAR(TODAY())) + (A1*7)-(WEEKDAY(VALUE("01/01/"&YEAR(TODAY())))+5)
Geoff

 

[PeterMoran]

Hi MOUSUSER,

I alluded to your point about being a week out in my last paragraph. Chip Pearson's formula assumes the first week of the year has to have a monday in it, while for 2002, for example, the Monday is actually in 2001. Try out different scenarios in Excel to ascertain exactly what you need. For example, if a Friday is the first day of the year does that week count as the first week, or is the next week the first week?

I was most impressed with Geoff's formula, so I immediately took it and tried it in different scenarios and found that it was not quite exactly what I thought you needed, hence my posting to advise you, and provide an alternative which you might find more suitable.

Good Luck!
Peter Moran

 

[xlhelp]

There is a whole can of worms here. If you say the first week of the year, thna isn't the first week from Tuesday to Monday for the current year? Then, if you assume the week to be starting on Sunday, then week one is going to be a partial week and week 52 is going to be a partial week. In the end it's all semantics.

BTW, Geoff, your last formula is the same as the one I posted.

Thanks.

Indu

 

[MOUSUSER]

Phew, so much information in so short a time and all so very useful.

Its been a real eye opener. What I thought was a simple question could raise all these answers. They will all be tried and tested thoroughly.

Linda