Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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)
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default 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)

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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)



  #5   Report Post  
Posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
 
Posts: 615
Default 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)



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Dates to Week Ending time period drew Excel Worksheet Functions 8 June 24th 08 04:09 PM
Entering week ending dates Jenilise Excel Discussion (Misc queries) 3 September 14th 07 12:04 AM
HELP PLEASE!! PROJECTING A WEEK ENDING PRODUCTION DATE Pete Excel Discussion (Misc queries) 0 February 5th 07 02:56 AM
How to format cells to show dates as the week-ending date of that dereksmom Excel Worksheet Functions 1 July 12th 06 04:40 PM
Rouding Dates to beginning of a week Peter W Excel Discussion (Misc queries) 3 February 16th 05 12:47 AM


All times are GMT +1. The time now is 12:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"