Last full week

[Svgmassive]

I am looking for a function that would return the last full week of the year.
thanks.

 

[Jonmo1]

You're going to need to provide more information...

Specifically, how do you define a full week??

Is it simply from Sunday to Saturday
Or Monday to Sunday
Or Monday to Friday
?

What exactly do you want to do once the last full week is found?
do you just want a list of all 7 dates within that week?


Assuming you mean a full week is Sunday to Saturday..
And you mean of the Current year..

Then put this in a cell, say A1
=DATE(YEAR(TODAY())+1,1,MATCH(7,WEEKDAY(DATE(YEAR(TODAY())+1,1,{1,2,3,4,5,6,7})),0))-13

That gives you the Sunday of the last full week
Then in A2 put
=A1+1
And fill down to A7


Hope that helps.

 

[Svgmassive]

Sunday to Saturday

 

[Jonmo1]

And are you just wanting to list all 7 dates within that week?

did you try my suggestion?

Excel Workbook
	E
1	12/25/2011
2	12/26/2011
3	12/27/2011
4	12/28/2011
5	12/29/2011
6	12/30/2011
7	12/31/2011
Sheet1

 

[Svgmassive]

I did it worked fine.what will be the weeknumber of today's date given that formula.Thanks

 

[Jonmo1]

Just put WeekNum around the formula...

=WEEKNUM(DATE(YEAR(TODAY())+1,1,MATCH(7,WEEKDAY(DATE(YEAR(TODAY())+1,1,{1,2,3,4,5,6,7})),0))-13)


The Weeknum function requires the analysis toolpack from Tools - Addins.