## [SOLVED] NOW() Function - too many decimal places

Discuss the spreadsheet application

### [SOLVED] NOW() Function - too many decimal places

Hi, first post and it's a question of course! Sorry.
I am using the "NOW()" function to calculate the days left to my birthday.
Here is the code i am writing:
Code: Select all   Expand viewCollapse view
`=NOW()-M27&" Days to my birthday"`

-M27 is the cell which contains my birthdate

It returns the correct amount of days including the extra "text" i have written.
However, no matter how i try to format the cell with the extra text included, it returns a result with 13 decimal places!?
Without the text however, it just returns the days left as it should, but i want to add that extra text in the same cell.

The cell is formated as a number as instructed in the help.

Many thanks for looking at my post and hoping for an answer please.
Last edited by robleyd on Tue May 21, 2019 12:46 am, edited 2 times in total.
Reason: Add green tick
OpenOffice 4.1.3 on MacOS Mojave 10.14.5

RayBir

Posts: 13
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

### Re: NOW() Function - too many decimal places

Use the TODAY() function instead of NOW()
NOW() includes the fraction of this date which has already passed (counting from midnight), shown as decimals in some cases, but usually translated to hours:minutes:seconds.
E.g. if now is noon, NOW() adds 0.5 to the day number.
Last edited by keme on Mon May 20, 2019 1:49 pm, edited 1 time in total.

keme
Volunteer

Posts: 3156
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: NOW() Function - too many decimal places

Use the function INT() in your formula.

Or use the function TODAY() instead of the NOW(). The result of the NOW() contains the time value in the fraction part, but the TODAY() does not.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.

Zizi64
Volunteer

Posts: 7965
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

### Re: NOW() Function - too many decimal places

keme wrote:Use the TODAY() function instead of NOW()
NOW() includes the fraction of this date which has already passed (counting from midnight), shown as decimals in some cases, but usually translated to hours:minutes:seconds.
E.g. if now is noon, NOW() adds 0.5 to the day number.

Thank you very much indeed, the "TODAY" function works perfectly with the text added!!!

Just to confirm the code that works:
Code: Select all   Expand viewCollapse view
`=TODAY()-M27&" Days to birthday"`
Last edited by RayBir on Mon May 20, 2019 8:41 pm, edited 1 time in total.
OpenOffice 4.1.3 on MacOS Mojave 10.14.5

RayBir

Posts: 13
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

### Re: NOW() Function - too many decimal places

Zizi64 wrote:Use the function INT() in your formula.

Or use the function TODAY() instead of the NOW(). The result of the NOW() contains the time value in the fraction part, but the TODAY() does not.

Thank you very much indeed, the "TODAY" function works perfectly, with text added as well! The INT() dosn't - but none the less, it now works!!

Just to confirm the code that works:
Code: Select all   Expand viewCollapse view
`=TODAY()-M27&" Days to birthday"`
OpenOffice 4.1.3 on MacOS Mojave 10.14.5

RayBir

Posts: 13
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

### Re: [SOLVED] NOW() Function - too many decimal places

When you concatenate numbers with text, you implicitly request that the number be converted to text first. This implicit conversion is the most likely reason why the INT() method suggested by Zizi64 didn't give the desired result. INT(NOW()) should return the same number as TODAY(), but the more complex expression may lead to Calc using a different number layout.

To have complete control of conversion, use the TEXT() function.
This should work, and is more reliable than your current formula (No need to change a working product. Just sayin' so you know about it if trouble should arise.):
Code: Select all   Expand viewCollapse view
`=TEXT(TODAY()-M27;"#0")&" Days to birthday"... or ... =TEXT(INT(NOW())-M27;"#0")&" Days to birthday"`
In many cases it is better to insert the text through formatting, or have it in an adjacent cell. This would keep the calculated day count as a number to use for further calculations. Use the Format code field.

Format code also supports "variable formats" on 3 levels, using 3 "code segments" separated by semicolon. (there is a 4th segment allowed, which will apply to text data in the cell.) The following format code may make sense, depending on your application:
Code: Select all   Expand viewCollapse view
`#0" days to birthday";#0"days passed since birthday";"Happy birthday!"`
By default the three segments apply to positive, negative and zero results respectively, but you can override those defaults.
Last edited by keme on Tue May 21, 2019 9:41 am, edited 1 time in total.

keme
Volunteer

Posts: 3156
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: [SOLVED] NOW() Function - too many decimal places

keme wrote:One additional comment:
When you concatenate numbers with text, you implicitly request that the number be converted to text first. This implicit conversion is the most likely reason why the INT() method suggested by Zizi64 didn't give the desired result. INT(NOW()) should return the same number as TODAY(), but the more complex expression may lead to Calc using a different number layout.

To have complete control of conversion, use the TEXT() function.
This should work, and is more reliable than your current formula (No need to change a working product. Just sayin' so you know about it.):
Code: Select all   Expand viewCollapse view
`=TEXT(TODAY()-M27;"#0")&" Days to birthday"... or ... =TEXT(INT(NOW())-M27;"#0")&" Days to birthday"`

In many cases it is better to insert the text through formatting, or have it in an adjacent cell. This keeps the calculated day count as a number, so it is useable for further calculations.

Thank you for that!!
Both work for me, however, i am confused. What do you mean by...
"In many cases it is better to insert the text through formatting, or have it in an adjacent cell."

Which text do you mean and how?
Do you mean that i insert a cell reference instead of the actual text? Like instead of writing "Days to my birthday" - i put in for example F21 - in which case how would that look please?
OpenOffice 4.1.3 on MacOS Mojave 10.14.5

RayBir

Posts: 13
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

### Re: [SOLVED] NOW() Function - too many decimal places

the text is "Days to my birthday".

To avoid confusion, you may safely disregard everything after your "Thank you" posts and the [SOLVED] tagging.

For possible enlightenment, read on and see the attached file.

Column F illustrates a case where your approach may give an issue, solved by either separating the text from number (adjacent cell) or using the format.

Again, if this is not relevant to your application of the formula, don't bother changing. If you are into learning, by all means inspect the format options.
Attachments
numbertext.ods

keme
Volunteer

Posts: 3156
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

### Re: [SOLVED] NOW() Function - too many decimal places

keme wrote:the text is "Days to my birthday".

To avoid confusion, you may safely disregard everything after your "Thank you" posts and the [SOLVED] tagging.

For possible enlightenment, read on and see the attached file.

Column F illustrates a case where your approach may give an issue, solved by either separating the text from number (adjacent cell) or using the format.

Again, if this is not relevant to your application of the formula, don't bother changing. If you are into learning, by all means inspect the format options.

Once again, many thanks!
I shall indeed study your document - you can never learn too much
OpenOffice 4.1.3 on MacOS Mojave 10.14.5

RayBir

Posts: 13
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden