A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Convert regular Date to Week Ending or Week Beginning Dates



 
 
Thread Tools Display Modes
  #1  
Old February 3rd 09, 09:39 PM posted to microsoft.public.excel.misc
Sam H
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)
Ads
  #2  
Old February 3rd 09, 09:50 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
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  
Old February 3rd 09, 10:12 PM posted to microsoft.public.excel.misc
Paul Wilson
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)



  #4  
Old January 27th 10, 02:30 PM posted to microsoft.public.excel.misc
Jim
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)

  #5  
Old January 27th 10, 03:27 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 07:50 PM.


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