Data merge not respecting formatting in .ODS but works fine in .CSV

[default_abuser]

Most of the prices I include in invoices are $XX format, no need for cents. It looks a lot nicer to just put $10, than to put $10.00, however occasionally I do have some that include cents, so my spreadsheet has a column that contains cells mostly set to $XX format, and some cells in the same column I set to $XX.XX format when the .XX is not .00.

Firstly, it would nice to learn an automatic or fast way of formatting a column this way. Presently, I set all of them to $XX.XX first,then carefully manually select the ones that should be just $XX and hit ctrl+1. Depending on the amount of data, this can be a time suck.

Secondly, even now that I’m doing it manually, if I save the spreadsheet in .ODS, when I pull the data into Writer with a data merge, all the figures are in $XX.XX format. When you View Data Sources, the data source preview pane shows all the cells in the $XX.XX format (even though in Calc you will see some are $XX.XX while most are $XX).

If I save in CSV – and not ODT – then the numbers are merged with the proper formatting. Seems to me this is a bug with .ODT. Is this a bug to report?

And do you know a fast way of making an entire column of prices formatted in $XX.XX unless the .XX is .00 in which case format those cells as $XX?

[EarnestAl]

My own observation: I like to see the full amount in an invoice, so $10.00. I am uncomfortable with a truncated total.

You need to make a rule as to when you include cents, there can be no automatic way to do anything without a process. Have you considered rounding?

BTW converting to text is similar to printing; you no longer have a dollar amount, only a dollar sign and some text.

You could use (for USD) the built in format [$$-409]#,##0.--;[RED]-[$$-409]#,##0.-- so for 5 entered you will see $5.--. If you remove everything in cents then rounding occurs so 5.49 becomes $5 and 5.50 becomes $6

[erAck]

A [$$-409]0.## format code is possible, which displays decimals only if they are not 0. However, it would not display $3.40 but $3.4

[Wanderer]

The formatting is “used” via csv, because you are actually doing two steps:
First you convert your numbers to text (save as csv), then you import this text again in writer.
Format-codes in Calc take part in the generation of the text “shown in the cell”, but don’t alter the value.
(Try to enter a Date, then change Formatting to Decimal-Number and back to Date for a Test.)
So imho - your observation is not a bug, but different behavior…

Workaround may be OLE-embedding, where the application which generated the table stays responsible
for its content, but I never tried this with Writer/Calc.

Your formatting is a bit unusual, but could achieved in Calc with Conditional formatting:
TestCondFormat.ods

In the appended file you may enter numbers in the first column, wich are shown to the right with different formatting (i added some colour to the conditional formatting). The test, if you have decimals is done by subtracting the rounded value, wich should result to 0, if there are no decimals.
(The other columns show formatting possibilities already shown in the comments.)

However: This only solves the problem in calc, not for direct export without csv…

J.

[default_abuser]

Thanks. I’ll use that. I’m trying to understand how it works. What does <> mean in the formula?