Convert regular Date to Week Ending or Week Beginning Dates
 

I am have column with dates that I would like to stratify by either Week
Ending Dates (Fridays or Saturdays) or Week Beginning Dates (Sundays or
Mondays)

Answer: Convert regular Date to Week Ending or Week Beginning Dates
 

Converting Regular Dates to Week Ending or Week Beginning Dates in Excel

1. Decide whether you want to use Fridays or Saturdays as your week ending dates, and whether you want to use Sundays or Mondays as your week beginning dates.
2. Create a new column where you will enter the week ending or week beginning dates. Let's say you want to enter the week ending dates in column B and the week beginning dates in column C.
3. In cell B2, enter the following formula:
   Code:

   ---

   =A2+7-WEEKDAY(A2,2)

   ---

4. Copy the formula in cell B2 and paste it into the rest of the cells in column B that correspond to the dates in column A.
5. In cell C2, enter the following formula:
   Code:

   ---

   =A2-WEEKDAY(A2,1)+1

   ---

6. Copy the formula in cell C2 and paste it into the rest of the cells in column C that correspond to the dates in column A.

Now you have a column with either week ending or week beginning dates, depending on your preference. You can use these dates to stratify your data by week.

Convert regular Date to Week Ending or Week Beginning Dates
 

I am not too sure what constitutes the beginning or ending of a week so I
will stich with generalities.

There is a weekday function which returns a number from 1 to 7 corresponding
to the day of the week. So this formula will give you the first day of the
week (assuming sunday is the first day of the week)
=A1-WEEKDAY(A1)+1
Add 7 to theat for the end of the week...

If your question is something different just reply back...


--
HTH...

Jim Thomlinson


"Sam H" wrote:

I am have column with dates that I would like to stratify by either Week
Ending Dates (Fridays or Saturdays) or Week Beginning Dates (Sundays or
Mondays)

Convert regular Date to Week Ending or Week Beginning Dates
 

Hi Sam

Follow this link
http://en.allexperts.com/q/Excel-105...nding-Date.htm


Paul

"Sam H" <Sam wrote in message
...
I am have column with dates that I would like to stratify by either Week
Ending Dates (Fridays or Saturdays) or Week Beginning Dates (Sundays or
Mondays)

Convert regular Date to Week Ending or Week Beginning Dates
 

I'm using this successfully, but have some cells with no data. Is there
something I can add that will leave the cell blank rather than display
#VALUE! or ###########

"Jim Thomlinson" wrote:

I am not too sure what constitutes the beginning or ending of a week so I
will stich with generalities.

There is a weekday function which returns a number from 1 to 7 corresponding
to the day of the week. So this formula will give you the first day of the
week (assuming sunday is the first day of the week)
=A1-WEEKDAY(A1)+1
Add 7 to theat for the end of the week...

If your question is something different just reply back...


--
HTH...

Jim Thomlinson


"Sam H" wrote:

I am have column with dates that I would like to stratify by either Week
Ending Dates (Fridays or Saturdays) or Week Beginning Dates (Sundays or
Mondays)

Convert regular Date to Week Ending or Week Beginning Dates
 

Maybe...

=if(a1="","",A1-WEEKDAY(A1)+1)

Jim wrote:

I'm using this successfully, but have some cells with no data. Is there
something I can add that will leave the cell blank rather than display
#VALUE! or ###########

"Jim Thomlinson" wrote:

I am not too sure what constitutes the beginning or ending of a week so I
will stich with generalities.

There is a weekday function which returns a number from 1 to 7 corresponding
to the day of the week. So this formula will give you the first day of the
week (assuming sunday is the first day of the week)
=A1-WEEKDAY(A1)+1
Add 7 to theat for the end of the week...

If your question is something different just reply back...


--
HTH...

Jim Thomlinson


"Sam H" wrote:

I am have column with dates that I would like to stratify by either Week
Ending Dates (Fridays or Saturdays) or Week Beginning Dates (Sundays or
Mondays)

--

Dave Peterson