Page 1 of 1
[SOLVED] NOW() Function - too many decimal places
Posted: Mon May 20, 2019 12:27 pm
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:
-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.
Re: NOW() Function - too many decimal places
Posted: Mon May 20, 2019 1:45 pm
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.
Re: NOW() Function - too many decimal places
Posted: Mon May 20, 2019 1:48 pm
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.
Re: NOW() Function - too many decimal places
Posted: Mon May 20, 2019 8:13 pm
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!!!
Just to confirm the code that works:
Re: NOW() Function - too many decimal places
Posted: Mon May 20, 2019 8:17 pm
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!!
Just to confirm the code that works:
Re: [SOLVED] NOW() Function - too many decimal places
Posted: Tue May 21, 2019 8:30 am
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.
Re: [SOLVED] NOW() Function - too many decimal places
Posted: Tue May 21, 2019 9:34 am
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?
Re: [SOLVED] NOW() Function - too many decimal places
Posted: Tue May 21, 2019 10:02 am
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.
Re: [SOLVED] NOW() Function - too many decimal places
Posted: Tue May 21, 2019 10:09 am
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
