Reverse of WEEKNUM 3

[xlhelp]

In my calculations I needed to get the date for the first day of the week knowing only the week number.

What I came up with is the formula below. There may be a better approach than mine and I would love to find out if there is. And, yes I do know about Date + 7.

=DATE(2008,1,1)+A2/52*365-WEEKDAY(DATE(2008,1,1))-6
where A2 has the number of the week.

Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

xl,

You might want to post that as a new FAQ.




Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

So you always want to use the current year?

Here's what I came up with:

[tab][COLOR=blue white]=INT((B1+(A2*7)-7)/7)*7+1[/color]
where B1 contains the first day of the current year.

Or, to make it self contained, something like
[tab][COLOR=blue white]=INT((DATE(YEAR(NOW()),1,1)+(A2*7)-7)/7)*7+1[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bluedragon2]

Would this work for you:

=A2*7-(WEEKDAY(39448)-1)-6

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlhelp]

blue, that gives me year 1900. I don't have time to play with it right now, but will try later.

Prof 'Iggins. Very smart. And shorter than mine. Thanks. [small](Man, I hate it when there are people smarter than me. LOL)[/small] Star for you.

Hi, Skip. That's a good idea. If there are more, I'll collect and post them as FAQ.

Member- AAAA Association Against Acronym Abusers

 

[bluedragon2]

Sorry, forgot the:

=39447+(A2*7-WEEKDAY(39448)-7)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[ck1999]

Cannot anotherhiggins code be simplified to this

=INT(DATE(YEAR(NOW()),1,1)+(A2*7)-9)


ck1999

 

[AnotherHiggins]

Niiiiice, CK1999!

But the only reason I have INT at the front of mine is to deal with dividing the starting date by 7. You don't even need the INT in yours, so just
[tab][COLOR=blue white]=DATE(YEAR(NOW()),1,1)+(A2*7)-9[/color]
will work!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[xlhelp]

ck1999, That works quite well. Thanks for shortening it even further. Star for you too.

bluedragon, I was trying to stay away from using serial number for date only because other people will be using this file and perhaps modifying it in the future. That's why combination of anotherhiggins and ck1999 solutions work better where it doesn't matter what year you are in.

Thanks, folks.

Member- AAAA Association Against Acronym Abusers

 

[bluedragon2]

I agree xl

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[mintjulep]

The -9 at the end works only for 2008 (or other years that start on Tuesday). To get it to work for any year.

=DATE(YEAR(NOW()),1,1)+(A2*7)-(6+WEEKDAY(DATE(YEAR(NOW()),1,1)))

 

[bluedragon2]

That is true Mint, that was my intent, just used serial dates.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlhelp]

The three formulas that work well are:

From anotherhiggins (best one of the three):
=INT((DATE(YEAR(H1),1,1)+(A2*7)-7)/7)*7+1

From mintjulep:
=DATE(YEAR(NOW()),1,1)+(A2*7)-(6+WEEKDAY(DATE(YEAR(NOW()),1,1)))

Mine (xlhelp) with some modifications:
=DATE(YEAR(NOW()),1,1)+A2/52*365-WEEKDAY(DATE(YEAR(NOW()),1,1))-6


Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

Oops, I was experimenting and left the formula in the spreadsheet as it was.

anotherhiggins formula should read:
=INT((DATE(YEAR(NOW()),1,1)+(A2*7)-7)/7)*7+1


Member- AAAA Association Against Acronym Abusers

 

[SkipVought]

BTW,

I have, for a long time now, used this formula to calculate the "Week Of" date, using the two variables to slide the starting point one way or another...
[tt]
=INT((TheDate+Var1)/7)*7+Var2
[/tt]
0, 0 yields a SATURDAY date
-2, 2 yields a MONDAY date.

Similar principle as y'all are pursuing.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[AnotherHiggins]

Indeed, Skip. That's what I based by formula on. Except I always use
"TheDate[highlight]-[/highlight]Var1". But it works on the same principle.

Taking your description a little further (for the "TheDate[highlight]-[/highlight]Var1" example since that's what I'm more familiar with), [ul]
[li]Var1 determines the day on which the change occurs[/li]
[ul][li]0 - change occurs on Saturday[/li]
[li]1 - change occurs on Sunday[/li]
[li]2 - change occurs on Monday[/li]
[li]3 - change occurs on Tuesday[/li]
[li]4 - change occurs on Wednesday[/li]
[li]5 - change occurs on Thursday[/li]
[li]6 - change occurs on Friday[/li]
[li]7 - change occurs on the following Saturday[/li][/ul]
[li]Var2 determines the day-of-week returned[/li][ul]
[li]0 - Returns Saturday[/li]
[li]1 - Returns Sunday[/li]
[li]2 - Returns Monday[/li]
[li]3 - Returns Tuesday[/li]
[li]4 - Returns Wednesday[/li]
[li]5 - Returns Thursday[/li]
[li]6 - Returns Friday[/li]
[li]7 - Returns the following Saturday[/li][/ul]
[/ul]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

John, "Great Explanations," a dickens of a post!

xlhelp ought to roll this into his FAQ as well.

Skip,

_{I'll be dressed to the nines this week, as I go to have my prostatectomy...
Because, if I'm gonna BE impotent, I want to LOOK impotent!}

 

[xlhelp]

FAQ for this thread has been posted. Thanks everyone for your contribution.

Member- AAAA Association Against Acronym Abusers