Cell formats, and using NOW() and other functions in OpenOffice Calc spreadsheets that you use in databases and mail merges

Mail merges work fine with spreadsheets as the data source. You create a spreadsheet of data, then choose File > New > Database, specify connecting to an existing database, Spreadsheet as the type, then click Next and point to the database itself.

Here are a couple things about formats, though.

Formats don't come through. So if you want your Salary field to have dollar signs, decimals, etc. you need to set the format. Likewise with date or time.

One way to do this is just set the formats when you view them. Press F4 and expand to view the query or table you want. Then just right-click on the column heading and choose Column Format.

Then in the window that appears you can select a category (Currency, Date, Number) and the the specific format you want. Click OK.

You can do the same thing in the database file itself. Open the .odb file, click the Tables or the Queries icon at the left, then double-click on the particular table (the sheet or query).

In the window that appears, right-click on the column heading, choose Column Format as before...

and in the window that appears, as before, make the choice you want. Click OK.

Now, here's a related topic. Can you use the =NOW() function in a spreadsheet and have it interpreted correctly in the database and in mail merges? (Thanks to a Colorado Springs LUG member for this idea.) The answer is Yes.

Here's a spreadsheet with =NOW() showing the absolutely current time and date.

Here's what it looks like, with nothing done to it, in the database view. It needs some tender loving formatting since it's just showing the internal numeric value. Right-click on the column heading and choose Column Format.

 

Set the format you want. The NOW() function can let you use a date or time format since it contains both.

 

And now it looks fine in the database view.

 

When I use this field in a mail merge....

Here's the output when I format the field as a date:

 

and when I format it as a time.

Comments

[Nathan]

Thanks for the info. I am trying to use this feature for a mail merge with time card information. In the spreadsheet, I have formatted the "time" cells to be [HH]:MM. This tells me the total number of hours worked.

However, when I apply the same format to my data source, it adds 48 hours to the total.

Do you have any ideas how I might correct this?

Additional information:
The spreadsheet file has many spreadsheets. The spreadsheet used as my data source is "Index". The total hours worked column that is used in the mail merge is actually a link to the total cell on the worker's individual page.

[Mary]

Just got Open Ofc 3.0. How do you make a page of address labels. Not from a database. Just manually putting in addresses for Christmas cards for instance. This seems like a very basic task but help is not helping me out any. (The old way of doing it was to go to Tools and go down to labels and envelopes.)

Thanks.