[Solved] Using Filter in Calc V3.1 with dates

[ChrisC]

I have been trying to find a way of using the Filter function in Calc (3.10) from a column of dates (using the Date format display in the form "Day Month Year" e.g. Mon Jul 27 2009) to extract all entries on a Saturday but Calc appears to recognise only the number equivalent of the date entries and I have not succeeded in finding a method of recognising the first 3 letters representing the date in the format displayed in each column cell.

Trying (a temporary) reformat to Text just converted all the date displays to their equivalent numbers and all my attempts to find if anyone had found a solution to this particular problem in several forums and using Google search have so far failed. Can anyone help with a solution, please?

Cheers, ChrisC

[Villeroy]

Add a calculated column like =TEXT($A$2:$A$999;"DDDD") and filter by that column.

[ChrisC]

Thank you for your input, Villroy. I tried your suggestion and realised I did not provide an important piece of information with my query viz. that the column of dates consists of non-consecutive days. My apologies for this omission but I think this means your suggestion would not help more than my crude alternative solution which was to add a column adjacent to the date column, in which I placed a character each time say Saturday occurred. With a column of about 1000 entries this was a bit tedious though it worked OK. It looks impractical to use your formula in this way and I realise I could include a unique letter for each day in the additional column and continue this as I add data. This would enable any day to be filtered but I hoped to find a way of manipulating the real date entries with the filter function for a neater and more general solution.
My apologies again for unintentionaly misleading you by my omission but if you have any further suggestions which might help I would be much obliged to receive them

Thank you, ChrisC

[Villeroy]

I'd strongly recommend to get familiar with the most fundamental concepts of spreadsheet usage. These concepts deal with the same basics across all applications since the 90ies. Any book similar to "Ecxel 95 for Dummies" will help you.

A database program has a steeper learning curve while doing all list-aggregation far more reliably and more easy to use. Extracting information out of lists is what databases are designed for.

Spreadsheets are numeric calculators in the first place. They calculate figures by positional references. The initial values may be imported from a database.
5 concepts to answer 90% of all Calc related questions in this forum:
Concept 1: [Tutorial] Absolute, relative and mixed references
Concept 2: Difference between value types numbers and text.
Concept 3: Difference between values and formatting.
Concept 4: Table layout for simple database tasks in a spreadsheet
http://en.wikipedia.org/wiki/Database_normalization
http://phlonx.com/resources/nf3/ (starting with a spreadsheet example)
http://support.microsoft.com/kb/283878
Concept 5: Looking up values in scalar and discrete context (LOOKUP and MATCH in ordered/unordered mode).

Attachment: Yet another quick and dirty spreadsheet demo on list aggregation by calculated fields, D-functions, SUBTOTAL and data pilot.

[ChrisC]

Thank you again, Villeroy, for your input. My data is spreadsheet material and, I think, not appropriate to a database appication. Your attachment provided me with a much better and easier solution than my "crude" manual input (though I have not yet found out how to allocate the cell dates to the variable 'Date' as in your example). I wonder, too, if the same approach might be made in a Filter operation (maybe the Advanced Filter) with a conditional expression. If so, this would avoid the need for the extra 'filter' column and provide the neater, more general solution I referred to. I plan to look into this further. However, your attachment provided a perfectly satisfactory work around so I will consider my query 'solved' and indicate this in the forum accordingly.

Thank you again,

ChrisC.

[Villeroy]

(though I have not yet found out how to allocate the cell dates to the variable 'Date' as in your example)

Manually: menu:Insert>Names>Define... provide a name and a reference. The rules of relative referencing apply to the given reference, relative to the currently active cell. Named "text values" and named constant numbers are possible as well.
Quick & easy: Select a range and type the name into the name box. This creates an absolute reference $SheetName.$A$1:$X$99
Take over from labels: This is what I did: Select the list, including the column headers and call menu:Insert>Names>Create... "Create from top row".
The database range named "List" is defined under menu:Data>Define... (absolute references to list ranges).
Such a simple, database-like layout is a prerequisite for many tasks in spreadsheets and similar programs. You may get results from cross-tables, schedules and similar structures, but this requires sophisticated and error-prone formulas.

My data is spreadsheet material and, I think, not appropriate to a database appication.

The list in my file (one column per attribute, one row per described item) is called a "normalized table" in database terms. Databases with such tables give you far more advantages than you imagine. Calc handles imported database tables (or derived row sets of such tables!) better than it's own flat data on the large, empty grid of cells. A database table has a first row and a last one, separated from column headers. A database refuses to store incomplete rows (missing values) and invalid data (e.g. text in numeric columns). The named import range adjusts automatically, including any adjacent range of calculated fields. Calc can create data pilots directly from database tables. Database tables can be filtered and sorted far more easily and flexibly than spreadsheets. To mention just a few advantages.

[acknak]

Calc handles imported database tables (or derived row sets of such tables!)...

Any pointers for doing this? I can't seem to do anything in Calc except drag static data from a table in the database explorer panel. Is it possible to somehow connect a database table or query as a sheet or range in Calc?

[Villeroy]

Calc handles imported database tables (or derived row sets of such tables!)...

Any pointers for doing this? I can't seem to do anything in Calc except drag static data from a table in the database explorer panel. Is it possible to somehow connect a database table or query as a sheet or range in Calc?

Download the attached spreadsheet and database.
No need to open the database. All you have to do with it is registering the file as datasource "PVC":
menu:Tools>Options... OOoBase>Databases > [New...] > point to the file and give the datasource name "PVC"

Then open the spreadsheet document at the first sheet, get toolbar "Form Navigation" and push it's last botton if you like to have a tabular form for free.
The second sheet shows a *slightly* advanced filter form together with a pink coloured calculated field and another pink cell with a standard deviation for the calculated field.
Third sheet imports a simple aggregation by categories and quarters of years.
4th sheet has a simple data pilot pulled directly from the database table.

P.S. I forgot to add a chart. Yes, charts do resize to import ranges as they grow and shrink.

[acknak]

Awesome! It seems to be working fine here; I will take some time to work through it.