• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

1st and last date require as per week number

N

Nisha Dhawan

New Member
Hello Everyone,

I have a data in which column A contains Year, column B contains Week_number and column C contains Date. In column D I need a formula to track the 1st date _last date for the particular week number. for ex:- If I Select Week number 40, column D contains the date 28-Sep-2014 to 4-Oct-2014.

Thanks in Advance
smile.gif

Nisha
 

Attachments

Hi Nisha ,

If you do not have any special requirement about the week numbering , then the easiest way is to have a table , and just lookup the required week.

In any unused column , say column Z , enter the dates from 1-1-2015 through 31-12-2015. In the next column , enter the formula =WEEKNUM(Z1) and copy down.

Now , just use these two columns to lookup the required week and get the corresponding dates.

For example if your week number is in cell A1 , and you want the start date in B1 , use the formula :

=INDEX($Z$1:$Z$365 , MATCH(A1,$AA$1:$AA$365,0))

In C1 , to get the end date , just use the formula :

=B1 + 6

Narayan
 
NARAYANK991 said:
Hi Nisha ,

If you do not have any special requirement about the week numbering , then the easiest way is to have a table , and just lookup the required week.

In any unused column , say column Z , enter the dates from 1-1-2015 through 31-12-2015. In the next column , enter the formula =WEEKNUM(Z1) and copy down.

Now , just use these two columns to lookup the required week and get the corresponding dates.

For example if your week number is in cell A1 , and you want the start date in B1 , use the formula :

=INDEX($Z$1:$Z$365 , MATCH(A1,$AA$1:$AA$365,0))

In C1 , to get the end date , just use the formula :

=B1 + 6

Narayan
Click to expand...
Hello Narayan,

Thanx for the reply, but my requirement is I want the formula in Column D to retrieve the 1st date _last date which is coming under the particular week number.
For Ex:- If I filter the Week num 40, then Column D should be fulfill the Value 28-sep-2014 to 4-Oct-2014.
 
Hi Nisha ,

I am not sure I have understood you ; try using the following :

=SUBTOTAL(105,$C$2:$C$51529)

=SUBTOTAL(104,$C$2:$C$51529)

Narayan
 
You must log in or register to reply here.
Back
Top