Remember Me?

#1
February 3rd 09, 10:39 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Feb 2009 Posts: 2
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
February 3rd 09, 10:50 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 5,942
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...

--
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
February 3rd 09, 11:12 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jan 2009 Posts: 12
Convert regular Date to Week Ending or Week Beginning Dates

#4
January 27th 10, 03:30 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 615
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...

--
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
January 27th 10, 04:27 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 35,218
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...

--
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post drew Excel Worksheet Functions 8 June 24th 08 04:09 PM Jenilise Excel Discussion (Misc queries) 3 September 14th 07 12:04 AM Pete Excel Discussion (Misc queries) 0 February 5th 07 02:56 AM dereksmom Excel Worksheet Functions 1 July 12th 06 04:40 PM Peter W Excel Discussion (Misc queries) 3 February 16th 05 12:47 AM

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