Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Peter W
 
Posts: n/a
Default Rouding Dates to beginning of a week

I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards
  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

Assuming the date is in A1, and the week starts on a Monday, then

=A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,-3,-4,-5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards



  #3   Report Post  
Sandy Mann
 
Posts: n/a
Default

Peter,

Assuming the actual start date of each week is Monday and you are not using
the 1904 date system then:

With any date within the the target week in A1

=A1-WEEKDAY(A1,3) will give the Monday start date. If the starting date is
a Sunday then it will give the previous Monday. If you want a Sunday to
show the next Monday, (ie the next day) then use:

=A1-WEEKDAY(A1,1)+2


HTH

Sandy

--
to e-mail direct replace @mailinator.com with @tiscali.co.uk


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards




  #4   Report Post  
Peter W
 
Posts: n/a
Default

Bob

Many thanks this works really, really well!!!!!

"Bob Phillips" wrote:

Assuming the date is in A1, and the week starts on a Monday, then

=A1+CHOOSE(WEEKDAY(A1),1,0,-1,-2,-3,-4,-5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter W" wrote in message
...
I have previously been using weeknum function to produce reports in week
number order. Users now wish to see the actual start date of each week. Is
there a standard function to do this or do I need to create an algorithm?

Regards




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
Dates by week, formula appeng Excel Worksheet Functions 5 February 6th 05 11:48 PM
Line or bar graphs for tracking stocks profit and loss. Mocity Charts and Charting in Excel 1 January 21st 05 02:21 AM
I need week number in excell from a date, first week must be mini. jPeich Excel Discussion (Misc queries) 4 January 5th 05 02:21 AM
first week of year - according to european standard soren Excel Worksheet Functions 3 November 2nd 04 08:21 PM
How do I ensure dates inputted are during the work week? Jim Johnson Excel Worksheet Functions 3 October 29th 04 08:25 AM


All times are GMT +1. The time now is 10:12 PM.

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"