0

Let me show you a sample data set:

enter image description here

I used the =WEEKNUM() formula to figure out what numbered week it was. Is there a formula to help me find out the ranges that a date follows under?

I gave several examples under the "Range" column to show what I mean.

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
dyao
  • 983
  • 3
  • 12
  • 25
  • So for an input of `3` you want an output of `1-12 to 1-18` (depending on the year), correct? – Werner Apr 22 '14 at 20:44
  • That is correct. For an input of `4` for example, I want it to show `1-19 to 1-25`. and for input of `5`, I want it to show `1-26 to 2-1`, etc etc. You don't have to worry about year, assume this is all in 2014 – dyao Apr 22 '14 at 20:46

2 Answers2

1
 =TEXT(A1-(WEEKDAY(A1,1)),"mm/dd/yyyy") & " - " & TEXT(A1-(WEEKDAY(A1,1))+7,"mm/dd/yyyy")

Or if you don't want the year:

 =TEXT(A1-(WEEKDAY(A1,1)),"mm-dd") & " - " & TEXT(A1-(WEEKDAY(A1,1))+7,"mm-dd")

This is calculating the beginning of the week using the WEEKDAY function. It then uses the same equation and adds 7 to get the end of the week. This only works with a full date that includes the year.

AxGryndr
  • 2,274
  • 2
  • 22
  • 45
0

Given to me by a gentlemen and it works:

=TEXT(A1-WEEKDAY(A1,1)+1,"MM/DD")&" to "&TEXT((A1+(7-WEEKDAY(A1,1))),"MM/DD")
dyao
  • 983
  • 3
  • 12
  • 25