Re: [libreoffice-users] dateValue() to dateText()?

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: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted