Apple Numbers use Actual date instead of formatted date I have a spreadsheet with a list of months as the columns with the value 1/1/2020, 2/1/2020, 3/1/2020, etc. The cell format has been set to January, February, March, etc.
When I attempt to use date for calculation, it uses the month name and not the date.
Example:
=SUMIFS($Amount,Date,&quot;&gt;=&quot;&amp;E1,Date,&quot;&lt;=&quot;&amp;EOMONTH(E1,0))

E1 contains '03/01/2020', displays March, but it sums everything up to the end of March. If I change it to &quot;&gt;=03/01/2020&quot; it works as expected.
Is there a way to force Apple Numbers to use the Actual value and not the formatted one? The documentation says it should be doing this, but it does not.
OK. I reproduced this. If you use CONCATENATE instead of &amp; to combine the strings then things become a little clearer. You can click on CONCATENATE and see the intermediate results--in this case, you will find a SUMIF with CONCATENATE in the February column yields &quot;&gt;=February&quot; so it is not surprising that the SUMIF produces an incorrect result.
So your supposition that Numbers is using the month name rather than the date is correct. If you put the T function around the date cell reference--i.e. CONCATENATE(&quot;&gt;=&quot;,T(C$1))--then you get an empty string from the T function, which matches with the T documentation (i.e. if you give T a non-TEXT type it returns an empty string). So, the T sees row 1 as being a series of DATE/TIME types but the CONCATENATE/&amp; sees it as a series of TEXT types.
I would consider this a Numbers bug (with CONCATENATE not recognizing date/time types). I seem to remember having done a similar thing in a prior release of Numbers and it worked as you desired so the bug may have been recently introduced. While we wait for a fix, let's see about a workaround.
I first tried DATEVALUE--i.e. CONCATENATE(&quot;&gt;=&quot;,DATEVALUE(C$1))--but that yielded &quot;&gt;=2/1/2020&quot;! I presume this is because DATEVALUE is seeing row 1 as a series of TEXT types just like CONCATENATE and when it sees February it assumes the day is 1 and the year is this year, hence 2/1/2020. I would consider this a variant on the first bug.
So we have to go the long way round. If you do this: CONCATENATE(&quot;&gt;=&quot;,DATE(YEAR(C$1),MONTH(C$1),DAY(C$1))) you will correctly get &quot;&gt;=2/1/2021&quot; and SUMIF produces the correct results.
Interesting problem.