Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

how to use a date like "TODAY" in a function without listening for the time in that date?

I'm creating a todo list. I know I shouldn't but it I got so stuck on this that I really want to know the answer. At the bottom of my table I have a footer row in which I want to show the total number of tasks to be accomplished today and each time I 'tick' a checkbox in one of the columns used for checking off my todos, I'd like to see it decrease by 1. But apparently from what I found out through experimentation is that the "TODAY" keyword or function word is using the date AND time and only if the time is 00:00:00 AM. So how can I avoid the time to be included when filtering? I'd like to use TODAY because then each day it'll reset itself automatically. Thank you for your time and effort in advance.

Numbers version: 3.5.3.

OS X: El Capitan 10.11

iMac, Mac OS X (10.5.8)

Posted on Nov 30, 2015 5:03 AM

Reply
16 replies

Nov 30, 2015 5:47 AM in response to BassPlaya

You can do something like this:

User uploaded file


First row is a header and

last two rows are footers



In this example,

D25=TODAY()

this is shorthand for, select cell D25, then type (or copy and paste from here) the formula:

=TODAY()


B25=COUNTIF(B,"="&D25)

B26=COUNTIFS(B,"="&D25, C, TRUE)


when you enter date, just enter the date (Numbers will add the time no matter what, but the time will always be "12:00:00" when you omit the time).

Nov 30, 2015 7:03 AM in response to VikingOSX

VikingOSX wrote:


When you set the Data Format for a cell, or range of cells, one can disable the time, and then the TODAY function will only report the date.



When you suppress display of the time it may look as if it's gone away. But a time is always included in the date-time string (which can cause mysterious failures to match). To see that, just select the cell and look lower left:


User uploaded file


SG

Nov 30, 2015 7:21 AM in response to VikingOSX

As SG posted this formatting only hides (or suppresses) the time... but the time still exists. This is because Numbers has a data type called a Date/Time value. You can never have one without the other.


If you enter a time only, Numbers adds the date on which you entered the time

If you entered a date only, Numbers adds the time for midnight ("12:00:00 AM")


This can cause confusion when dealing with entering times (especially on different days).


If you entered the time "7:01 AM" yesterday, then change the time to "7:10 AM" today


the actual value stored will be "11/29/2015 7:01 AM" and "11/30/2015 7:10 AM"


when comparing date/time values this can cause problems

Dec 1, 2015 3:47 AM in response to BassPlaya

If each task includes a desired time of completion, not just the date, you can use another column (which you will hide after it is all set up) to strip the time off of it using the DATEVALUE function. Actually it will set the time to the default of midnight, same as what the TODAY function will give you. You can then use that column to determine how many tasks are due or complete for today.

Dec 1, 2015 3:48 AM in response to BassPlaya

BassPlaya wrote:


So how can I avoid the time to be included when filtering? I'd like to use TODAY because then each day it'll reset itself automatically.


Here's another way you can filter for today's date without worrying about whether there are time values in the cells:


User uploaded file


=COUNTIFS($A,">="&TODAY(),$A,"<"&TODAY()+1,$B,FALSE)


This counts rows unchecked in column B whose date-time in column A is on or after the past midnight (TODAY) but before the coming midnight (TODAY+1).


SG

Nov 30, 2015 9:22 AM in response to Wayne Contello

Wayne Contello wrote:


OR:


=COUNTIFS($A,">="&TODAY(),$A,"<="&TODAY(),$B,FALSE)


Hi Wayne,


I think you need the TODAY()+1 in one of the column-condition pairs if you want to count rows where in the time-value in column A there is a time other than 12:00 AM, i.e. count everything starting from the past midnight (TODAY) up to but not including the next midnight (TODAY+1).


SG

Dec 1, 2015 3:55 AM in response to SGIII

Hey SGIII,


Your answer worked as well. There are several good solutions to this problem apparently. I'm happy to get so much great information from you guys. The only thing I don't quite get is the & symbol. I would use it for concatenating words like "tasks done" at the end of the formula, which works fine. But why would you say ">="&TODAY()? Can you elaborate? I my search for "&" in the functions browser didn't give me a result. Cheers!

Dec 1, 2015 5:10 AM in response to BassPlaya

Hi BassPlaya,


my search for "&" in the functions browser didn't give me a result.

The COUNTIFS function follows this syntax (from the Function Browser):


The COUNTIFS function returns the number of cells in one or more collections that satisfy given conditions (one condition per collection).

COUNTIFS(test-values, condition, test-values…, condition…)

  • test-values: A collection containing values to be tested. test-values can contain any value.
  • condition: An expression that can include comparison operators, constants, the ampersand concatenation operator, and references. The contents of the condition must be such that the result of comparing the condition to another value results in the boolean value of TRUE or FALSE.
  • test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a conditionexpression. This pattern of test-values, condition can be repeated as many times as needed.
  • condition…: If an optional collection of test-values is included, an additional expression that results in a boolean TRUE or FALSE. There must be one condition expression following each test-values collection; therefore, this function will always have an odd number of arguments.

Each condition is a text string, hence a comparison operator (such as >= in double quotes), then the ampersand concatenation operator (&) to join the comparison operator to a value, TODAY() as a text string.

User uploaded file


Hi SG,


If you want TODAY+1, just come to Australia.

(Don't worry that the world might end tomorrow. It's already tomorrow in Australia 😉.)

User uploaded file

Sorry, BassPlaya. Just joking.


Regards,

Ian.

Dec 1, 2015 6:26 PM in response to Yellowbox

Yellowbox wrote:


Hi SG,


If you want TODAY+1, just come to Australia.

(Don't worry that the world might end tomorrow. It's already tomorrow in Australia 😉.)



I figure it's NOW()+16/24 in Australia so there's still time for the world to end before TODAY()+1. No, wait, that's not right. It seems, though I haven't thought this through, that TODAY()+1 can, but does not always, come before NOW()+16/24 or even Hong Kong's NOW()+13/24. All so confusing! Why can't everybody just speak the same language (mine of course) and go by the same time around here?😉


SG

how to use a date like "TODAY" in a function without listening for the time in that date?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.