William Case wrote:
On Fri, 2006-11-08 at 23:07 +0200, Andreas Saeger wrote:
William Case wrote:
Hi;
Not exactly, but I could copy and paste.
First day of week for some month:
=DATE(myYear;MyMonth;1)+MOD(myDoW-WEEKDAY(DATE(myYear;MyMonth;1));7)
where myYear and myMonth specify the month in question and myDoW is a
day-of-week-number as returned by WEEKDAY(Date). Sun=1,...Sat=7.
Same with last day of week for some month:
=DATE(myYear;MyMonth+1;0)-MOD(WEEKDAY(DATE(myYear;MyMonth+1;0))-myDoW;7)
Test with some named style having a date-format like YYYY-MM-DD NN
(2006-06-02 Fr)
---------------------------------------------------------------------
The 0 (0.000000) date value still gives me '30/12/1899'. So there is no
problem there, but thanks for the tip of using zero as a start date
rather than 30/12/1899. Cleans things up.
You mentioned it shows 29th Dec 1899. This indicates a value <0 and >-1.
I appreciate your sharing your formulae, but WEEKS() seems neater and
cleaner and has the advantage of being built in, if only I could be
certain that the Type in WEEKS(Start Date; End Date; Type) meant what I
think it means.
Well, you need to be shure what it does:
Put this into a test-sheet:
A1: =TODAY() [or any other start-date]
A2: =A1+1
B1: =WEEKS($A$1;$A1;0)
C1: =FLOOR($A1-$A$1;7)/7
D1: =WEEKS($A$1;$A1;1)
E1: =FLOOR($A1-$A$1+WEEKDAY($A$1;3);7)/7)
[WEEKDAY(Dt;3) starts with Monday=0)]
Copy down B1:E1 to row B2:E2, then copy down row 2 to a few dozends of rows.
Change A1 (add/substract some number of days)
Format A, so it shows a weekday, for instance YYYY-MM-DD NN
WEEK(Dt;0) gives count of complete weeks since start, WEEK(Dt;1) seems
to start counting at next Monday.
I am going to apply your formulae -- after dinner -- and see if I end up
at the same place.
Thanks again.
You're welcome.
P.S. You are right. At least WEEKDAY(Dt;1) is more elegant than
floor(...) if you know what it does.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
