0

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,">="&E1,Date,"<="&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 ">=03/01/2020" 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.

2
  • I think that this is the way it supposed to work. Can you refer to part of documentation which says otherwise? If cell is formatted as month name ('January' from Date dropdown) dragging it changes month name and keeps day in tact. If cell is formatted as date ('2020-01-05' from Date dropdown) then dragging changes day. As condition includes equal part then it will match all days of month March. Dec 21 '20 at 18:05
  • The way this is worded makes it seem like date/time cells will always use the Actual value. "The Smart Cell View at the bottom of the window shows you the actual value of both the date and the time for a selected cell, even if you choose to show only one in the table." support.apple.com/guide/numbers/… Dec 21 '20 at 18:52
1
+50

OK. I reproduced this. If you use CONCATENATE instead of & 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 ">=February" 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(">=",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/& 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(">=",DATEVALUE(C$1))--but that yielded ">=2/1/2020"! 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(">=",DATE(YEAR(C$1),MONTH(C$1),DAY(C$1))) you will correctly get ">=2/1/2021" and SUMIF produces the correct results.

Interesting problem.

3
  • It looks like it will still work with & operator as long as you use the DATE function to create the value. I originally tried DATEVALUE as well and gave up this line of thinking. Either way, I have the solution. Thank you! Dec 29 '20 at 19:37
  • 1
    Great. The CONCATENATE is in there as a debugging tool. I have found being able to see intermediate results with it has turned out to be so useful I use CONCATENATE all the time. Dec 29 '20 at 21:03
  • Per the DATEVALUE help (at least in v5.1), "The value returned is formatted according to the current cell format." And since the value of a month is always its first day…. As an aside, if you change the formatting so that C1 displays '2021' instead of 'February', the function will return '2021-01-01', or the first day of the year — kind of fun in an abstract sense. So apparently, it's working as designed and you couldn't use DATEVALUE in a non-current year unless you change the formatting to include both year and month, e.g. 'Date: 2020 January'.
    – Mockman
    Dec 31 '20 at 8:51
-1

Is E1 really a date? When you copy in data from external sources sometimes dates can paste in as characters.

3
  • Yes. I entered it in manually and on the bottom where it shows the Actual value it will appear as 3/1/2020. Dec 27 '20 at 17:47
  • Chris, it works for me the way you want it to. I am on Numbers 10.3.5 and macOs 11.0 on a MacBook Air M1. Can you provide the data? Dec 27 '20 at 18:30
  • I put together a sample spreadsheet. The dates where entered as m/d/yyyy, then I changed the Data Format of each to show what happens. Screenshot of Data Dec 28 '20 at 1:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .