how to use NOW() in numbers

Question

Level 1

0 points

how to use NOW() in numbers

Hi.. I've recently switched from the dark side to Apple and am converting an Excel spreadsheeet. The spreadshet keeps track of subscriptions, things that a re due once a year, etc.. One of the spreasheet's formulas uses 'NOW()'.. The user can specify a day-range, like 15, so that the spreadsheet would highlight due dates within 15 days.. MS handles dates in serial numbers which allows date comparisons ( I'm guessing you already know all of this ) .. anyway, For example, the user enters the renewal or due date in a certain cell and then the next cell would subtract that date's serial number from the current date's serial number using something like 'B3-NOW()'.. The resulting number could be cast to an integer and compared to the '15' .. and if the result is less than that, aka 'due within 15 days', then the cell would be highlighted red.. I gave up on the conditional formatting but I don't know how to use the equivalent of 'NOW()'.. Any help is appreciated.. Thanks!!! -ps, I googled this but couldn't find anything helpful..

iPhone 3G, Mac OS X (10.7.3), Excel to Numbers

Posted on Feb 11, 2012 3:00 PM

Reply

Answer 1

carl wolf

Level 6

14,631 points

Feb 11, 2012 4:12 PM in response to mj0770

Why do you want to use something that is "equivalent to NOW()"? Microsoft Excel on Macs use the "NOW()" notation.

Reply

Answer 2

Feb 11, 2012 11:04 PM in response to mj0770

Numbers does provide the NOW() function that returns the current date & time. The details for this function, and many others, are available in the HELP menu --> iWork Formulas and Functions Help.

To get the number of days between the renewal date and the current date, try using the DAYS360 function in Numbers to return the difference..... your example formula above, 'B3 - NOW()' would become 'DAYS360(B3, NOW())' . You can then compare this result to your '15' day range. Please note that if you use a cell to contain the comparison day range, that cell must be formatted in the Inspector as a 'duration' with units of 'day' for the compare to work. Incidentally, the conditional formatting will work if you use this 'duration' formatted cell in the Conditional Format rule.

Hope this helps.

Rob

Reply

Answer 3

mj0770 Author

Level 1

0 points

Feb 12, 2012 4:14 PM in response to mj0770

Rob, that is very helpful!! and thanks for the easy-to-follow example of how to use it.. I'd be lying if I said I didn't feel a little foolosh by finding out the Numbers actually does have the NOW() functionality - but, I also know that feeling comes with being new to it.. and a nice added bonus to find out conditional formatting will work too.. very cool stuff!!

Also, thanks for the links to where to find more info on this and other functions..

Thanks again!!- -Mike

Reply

Answer 4

Apr 20, 2016 2:55 PM in response to Rob Crestani

Rob,

I know this has been awhile since you posted here, but I just can't figure this out for the life of me.

I have an expiry date in cell B5, and cell E5 for days remaining to expiry, and have used both the NOW and DAYS360 formula's and have the same problem.

1) the days difference between the expiry date and the present date show e.g. -84, when it should show just 84 (so I think).

2) conditional highlighting, duration format, is not responding at all.

I'm using Numbers 3.6.1; any thoughts on this?

Tab

Reply