Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 3rd 09, 09:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
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  
Old February 3rd 09, 09:50 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,942
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)

  #3   Report Post  
Old February 3rd 09, 10:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
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)



  #4   Report Post  
Old January 27th 10, 02:30 PM posted to microsoft.public.excel.misc
Jim Jim is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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)

  #5   Report Post  
Old January 27th 10, 03:27 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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 01: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 15th 05 11:47 PM


All times are GMT +1. The time now is 01:37 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017