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

Discuss the spreadsheet application
Post Reply
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

Post by RayBir »

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

=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 Catalina 10.15.1
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: NOW() Function - too many decimal places

Post by keme »

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.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: NOW() Function - too many decimal places

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: NOW() Function - too many decimal places

Post by RayBir »

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!!! :bravo:

Just to confirm the code that works:

Code: Select all

=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 Catalina 10.15.1
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

Re: NOW() Function - too many decimal places

Post by RayBir »

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!! :bravo:

Just to confirm the code that works:

Code: Select all

=TODAY()-M27&" Days to birthday"
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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 if trouble should arise.):

Code: Select all

=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

#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.
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

Post by RayBir »

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

=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 Catalina 10.15.1
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Post by keme »

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
(12.77 KiB) Downloaded 95 times
User avatar
RayBir
Posts: 20
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

Post by RayBir »

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 :super:
OpenOffice 4.1.3 on MacOS Catalina 10.15.1
Post Reply