OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Data filter

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
pt
General User
General User


Joined: 16 Dec 2005
Posts: 18

PostPosted: Mon Dec 19, 2005 9:11 am    Post subject: Data filter Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 19, 2005 10:28 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
BookLady
Super User
Super User


Joined: 03 Nov 2004
Posts: 727

PostPosted: Mon Dec 19, 2005 12:03 pm    Post subject: Reply with quote

Sorry but I have no idea what Villeroy just posted. Embarassed 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.
Back to top
View user's profile Send private message
pt
General User
General User


Joined: 16 Dec 2005
Posts: 18

PostPosted: Mon Dec 19, 2005 1:47 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 19, 2005 1:56 pm    Post subject: Reply with quote

BookLady wrote:
Sorry but I have no idea what Villeroy just posted. Embarassed 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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 19, 2005 1:57 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group