| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
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. |
|
| Back to top |
|
|
BookLady
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. |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
|
