Date: prev next · Thread: first prev next last
2014 Archives by date, by thread · List index

At 20:04 05/09/2014 -0700, Ross Noname wrote:
I have a spreadsheet to keep track of payments. In one column I will type 'ctrl+;' to enter the date. These cells are formatted for date and duly show the date. If a date is entered the amount received will appear in the adjacent cell (the amount is the same for everyone, so it's part of the cell formula). On a separate sheet I keep track of payments due. These cells check for entries in the 'Date' column. If the 'Date' column cell is blank, they show amount due (requiring 'number/currency' formatting); if there is an entry in the 'Date' column cell (ie. there has been a payment) I want the cell to show the date from the 'Date' column.

It's reasonable to wonder whether this is the best way to design your spreadsheet. You say the amounts are all the same, so there would appear to be no need to have the amounts listed there at all: you could have blank cell or just the word "Unpaid" or whatever. It's tempting to wonder if you are intending to do further calculations using these amounts, perhaps totalling them. But that cannot be so, as you would end up summing a lot of fixed currency amounts along with a number of dates - which makes no sense.

One obvious way to handle this - if you really do want to see lots of identical (currency) values - is to break your second sheet data into two columns: one for the date and the other for the amount. Each row would have either a date in one column or the amount in the other. What's wrong with that?

However, because the cells are formatted for 'number/currency' I get a number.

One simple solution is *not* to format these cells as currency. If the cells are unformatted, you can display a mixture of date and currency values which are copied (using a formula) from other cells already containing date and currency values, which is exactly what you say you want. Set the formatting of the result cells back to "General" to see what I mean.

Under formulas we have dateValue(). What is the opposite of that? Why isn't there a dateText() formula?

Probably because it is unnecessary: the TEXT() function already does what you need. You have a date value in your source cell and imply that you would be happy with not a true date value but instead a string showing that date in your result cell. If so, all you need to do in your formula that copies the date is to replace Xn (representing the source cell) with something like TEXT(Xn;"NN D MMM YY"). You can adjust the format string to suit, of course. You can find details in the help text, or you may find it easier to select a particular date format in the Format Cells dialogue and note the "Format code" listed at the bottom of the dialogue window.

As you already understand, such a text string can be happily displayed in a column formatted as currency or whatever.

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.