Hi Andreas; Are you a contributor or moderator for this OOo user list?
I ask, not to pry, but because I think I have found a basic problem with dating in OOo - Calc at least. On Sat, 2006-12-08 at 02:42 +0200, Andreas Saeger wrote: > William Case wrote: > > On Fri, 2006-11-08 at 23:07 +0200, Andreas Saeger wrote: > >> William Case wrote: > >> --------------------------------------------------------------------- > > 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. As you suggested I created a test sheet. My first entry was A1=N(0) formatted NN MMM D YY. A1 at first showed Sat Dec 30, 99. Then a few seconds later when Calc auto-saved. A1 then showed Fri Dec 29, 99. A1 remained equal to =N(0). To test it, I deleted the A1 value and re-entered it, =N(0) and got Sat Dec 30, 99 back. I immediately used F9 to recalculate and got no change. I then waited for the automatic save and A1 changed again to Fri Dec 29, 99. > > 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 re-entered the A1 =N(0) value and proceeded to input your suggested table. I set B1 to TODAY(). Row 1 values were 0; row 2 values were all 5563 (number of weeks from the turn of the last century to today). No change was detected in these numbers during auto-save. But then they shouldn't reflect a fraction of a week. I set up two additional columns (F & G); F contained the date value for A in each row; G contained date value divided by 7. As the A1 value changed after the auto-save so did the value of column G (at least the fraction part of the calculation changed). Even if I am wrong about tying this to the auto-sav, something is happening to date calculations that goes beyond the proper use of the WEEKS() function. By the way, throughout this whole precess Type 0 or 1 in WEEKS() didn't seem to make a difference. Both types made their calculations based on the Sat Dec 30, 99 or Fri Dec 29, 99 Start date. Neither made a distinction between being inclusive or a count. I must have tried 40-60 different combinations of dates and formulae. I am using Fedora Core 5 and OOo 2.0.2, but I haven't had problems with timing in my system or in any other programs. > > You're welcome. > P.S. You are right. At least WEEKDAY(Dt;1) is more elegant than > floor(...) if you know what it does. P.S, As I type, I realize I didn't experiment with a completely different Start date, just additions and subtractions of whole numbers to =N(0). -- Regards Bill --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
