Formula

[Guest]

I have a range of dates on one worksheet e.g 1 Jan 07 - 7 Jan 07 which equals
week 1, 8 Jan 07- 14 Jan 07 which equals week 2 as so on so that all 52 weeks
of the year are covered.

In another sheet I have a date e.g. 3 Jan 07. I want to lookup the range
above and return the value in the week column. Is there a formula that I can
use to do this?

Thanks

 

[Guest]

I have a range of dates on one worksheet e.g 1 Jan 07 - 7 Jan 07 which equals
week 1, 8 Jan 07- 14 Jan 07 which equals week 2 as so on so that all 52 weeks
of the year are covered.

In another sheet I have a date e.g. 3 Jan 07. I want to lookup the range
above and return the value in the week column. Is there a formula that I can
use to do this?

Thanks

I THINK YOU DO NOT REQUIRED ANY DATA BASE TO LOOK UP THE VALUE OF
DATE.THIS CAN BE SOLVE WITH THE HELP OF BELOW MENTIONED FORMULA.
IF CELL A1 CARRIES ANY DATE THEN THIS FORMULA WILL GIVE YOU WEEK NO.

=CONCATENATE("week"," ",INT((A1-39083)/7)+1)

 

[Pete_UK]

No need to look it up - you can calculate it with this:

=INT((A2-DATEVALUE("1/1/2007"))/7)+1

if your date is in A2.

Hope this helps.

Pete