Auto Create Weekly Date Range Base On a Date

[Kintantay]

In column A I have dates 08/01/2011 to 11/30/2011. Is there a formula that will look at the date in cell and return a date range in column B. Eg. Column A has
A1 08/01/2011
A2 08/10/2011
A3 08/22/2011
Column B should return
B1 08/01/2011 – 08/05/2011
B2 08/08/2011 – 08/12/2011
B3 08/22/2011 - 08/26/2011

Is this possible?

Thanks in advance

Kin

 

[Kintantay]

The formula you provided is a life saver and I thank you. Could provide a break-down of how the formula works.

Kin

 

[T. Valko]

The formula you provided is a life saver and I thank you. Could provide a break-down of how the formula works.

Kin

=TEXT(A1-WEEKDAY(A1,2)+1,"m/d/yyyy")&" - "&TEXT(A1-WEEKDAY(A1,2)+5,"m/d/yyyy")

This portion of the formula returns the Monday date of the week based on the date entered in cell A1:

A1-WEEKDAY(A1,2)+1

In Excel dates are really just numbers formatted to look like dates so that we as human beings will recognize the entry as a date.

These numbers which are called date serial numbers are simply the count of days since a base date. The default base base date is Jan 1 1900. Jan 1 1900 is date serial number 1. The date serial number increases by 1 for each successive day since the base date. Like this:

1/1/1900 = date serial number 1
1/2/1900 = date serial number 2
1/3/1900 = date serial number 3
1/4/1900 = date serial number 4
1/5/1900 = date serial number 5
1/1/1975 = date serial number 27395
12/31/2010 = date serial number 40543
8/9/2011 = date serial number 40764

These date serial numbers are the true underlying value of the cell. If you type in a date like 8/9/2011 Excel recognizes that you want this this to be a date and it automatically formats the cell as date. However, the true value of the cell is the numeric date serial number. 40764. You can see this true cell value by changing the cell format to General or Number.

OK, so we have the date for the Monday now we need the date for the Friday.

This portion of the formula returns the Friday date of the week based on the date entered in cell A1:

A1-WEEKDAY(A1,2)+5

Then we add the dash between the dates:

&" - "&

A1-WEEKDAY(A1,2)+1&" - "&A1-WEEKDAY(A1,2)+5

Since this returns a TEXT value Excel can't automatically format the date serial numbers as dates so at this point the formula result would look something like this:

40469 - 40473

So, we have to use the formula itself to format the date serail numbers so we can recognize them as dates. We do that using the TEXT(...) function.

TEXT(A1-WEEKDAY(A1,2)+1,"m/d/yyyy")
TEXT(A1-WEEKDAY(A1,2)+5,"m/d/yyyy")

=TEXT(A1-WEEKDAY(A1,2)+1,"m/d/yyyy")&" - "&TEXT(A1-WEEKDAY(A1,2)+5,"m/d/yyyy")

So, the final result of the formula might be something like this:

10/18/2010 - 10/22/2010

 

[Kintantay]

Thank you very much!!

 

[T. Valko]

Thank you very much!!

You're welcome!