[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
pt
General User

Joined: 16 Dec 2005
Posts: 18

 Posted: Mon Dec 19, 2005 9:11 am    Post subject: Data filter Data filter seems to function a bit differently than Excel. Trying to filter data in a column that references another column. Column C is named Day and Column D is named Date. D10 contains a date such as 06/17/2005 and C10 contains =Weekday(D10) and is formatted as custom "ddd" in order to return the value Fri in this case. Excel would filter by text string in the column but do not see that Calc does that and am unable to return rows that have "Fri" in Column C.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

 Posted: Mon Dec 19, 2005 10:28 am    Post subject: Wait a moment! You are not calculating what you want to calculate. Today() = Monday 19/12/05 Weekday(Today()) = 2 (without 2nd parameter Sunday = 1) 2 formatted as some long date format = Moday, January 1st 1900 00:00:00 You are calculating 30/12/1899 + days since last saturday. Day Zero = 1899-12-30 Day 1 = 1899-12-31 Day 2 = 1900-1-1 [...] N(Today()) = 38705 Create your weekdays =TEXT(D10;"NNN") You will get a text-value of D10, showing the weekday.
Super User

Joined: 03 Nov 2004
Posts: 727

 Posted: Mon Dec 19, 2005 12:03 pm    Post subject: Sorry but I have no idea what Villeroy just posted. If that solves the problem good. But you might try putting the following in the column for DAY =choose(weekday(L3);"SUN";"MON";"TUE";"WED";"THU";"FRI";"SAT") where L3 is the cell in the column for DATE with either the formula =Today() or the actual date. Then no matter how you sort the DAY column will always reflect the correct day for the DATE column. This works with Data>Filter> Standard where Field Name is DAY and Condition is = and the text FRI is typed in.
pt
General User

Joined: 16 Dec 2005
Posts: 18

 Posted: Mon Dec 19, 2005 1:47 pm    Post subject: I am sorry I am having difficulty understanding the first option. The second option works well, thank you. Is there a similar function for Month as there is for Weekday?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Mon Dec 19, 2005 1:56 pm    Post subject:

 BookLady wrote: Sorry but I have no idea what Villeroy just posted. If that solves the problem good. But you might try putting the following in the column for DAY =choose(weekday(L3);"SUN";"MON";"TUE";"WED";"THU";"FRI";"SAT") where L3 is the cell in the column for DATE with either the formula =Today() or the actual date. Then no matter how you sort the DAY column will always reflect the correct day for the DATE column. This works with Data>Filter> Standard where Field Name is DAY and Condition is = and the text FRI is typed in.

He/she has date 2005-06-17 in D10 and calculates in C10 =weekday(D10), which is 6 (friday, because 1st-day is sunday). Then he applies format "NNN" to number 6 rather than 2005-06-17.
Apply some long date format to the value 6 and you'll see that 6 is Jan 1st 1900 (6 days after day zero), which happens to be a Friday too. But the Friday was nearly 106 years ago.
When you do one of the following steps:
1. Change C10 to =weekday(D10;0), which sets monday to be the zeroth day of a week
2. =weekday(D10;2), which sets monday to be the first day of a week.
3. Change the zeroth day in the calc-options
Then you get wrong weekdays.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Mon Dec 19, 2005 1:57 pm    Post subject:

 pt wrote: I am sorry I am having difficulty understanding the first option. The second option works well, thank you. Is there a similar function for Month as there is for Weekday?

It's called MONTH()
Press Ctrl+F2 and select category Date/Time from the functions list.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum