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

Discuss the spreadsheet application

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

Postby RayBir » Mon May 20, 2019 12:27 pm

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

Re: NOW() Function - too many decimal places

Postby keme » Mon May 20, 2019 1:45 pm

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
keme
Volunteer
 
Posts: 3216
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: NOW() Function - too many decimal places

Postby Zizi64 » Mon May 20, 2019 1:48 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8152
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: NOW() Function - too many decimal places

Postby RayBir » Mon May 20, 2019 8:13 pm

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

Re: NOW() Function - too many decimal places

Postby RayBir » Mon May 20, 2019 8:17 pm

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   Expand viewCollapse view
=TODAY()-M27&" Days to birthday"
OpenOffice 4.1.3 on MacOS Mojave 10.14.5
User avatar
RayBir
 
Posts: 18
Joined: Mon May 20, 2019 12:10 pm
Location: Sweden

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

Postby keme » Tue May 21, 2019 8:30 am

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

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

Postby RayBir » Tue May 21, 2019 9:34 am

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

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

Postby keme » Tue May 21, 2019 10:02 am

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 21 times
User avatar
keme
Volunteer
 
Posts: 3216
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby RayBir » Tue May 21, 2019 10:09 am

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 8 guests