Sumifs not returning correct value for a date range?

Hi Michael,

Could you experiment a little and advise which formats Numbers will convert to date & time for you?

For example, if you type a single quote followed by Mar 07, 2011 that should be text in the cell. Can you then take that cell and format it as Date & Time?

How about a single quote followed by 2011-03-07? Will that convert?

Or does some other format convert?

Once we know one or two formats that Numbers will convert for you then it shouldn't be too hard to suggest a relatively short AppleScript to convert your existing "date" strings into one of those formats, which you can then have Numbers format as Date & Time. Running an AppleScript can be as simple as copying and pasting a script from a post here into AppleScript Editor, then clicking the green triangle 'Run' button.

If you find Numbers won't convert any format then it wouldn't hurt to try uninstalling it and reinstalling. To uninstall go to LaunchPad, hold down the option key until the icons quiver, then click the x by the Numbers icon with the four columns. Then go to the Mac App Store and redownload.

SG

Ok, if your "dates" are all uniformally in the format Mar-07-2011, then the following script will convert them to Mar 07, 2011, which you can then format in Numbers as Date & Time. This assumes they are all uniform. If they vary then a more elaborate script will become necessary.

As this overwrites existing content, I suggest trying this on a copy of your original document.

1. Copy the script and paste it into AppleScript Editor.
2. Select the cells with the "dates" in format Mar-07-2011.
3. Click the green triangle 'Run' button.
4. Format the cells as Date & Time.

SG

--converts string from Mar-07-2011 format to Mar 07, 2011 format

--select cells with Mar-07-2011 format, run script, format cells as Date & Time

try

tell application "Numbers" to tell front document to tell active sheet to tell (first table whose class of selection range is range)

tell selection range to repeat with aCell in cells

set aValue to value of aCell

set myDay to text 5 thru 6 of aValue

set myMonth to text 1 thru 3 of aValue

set myYear to text 8 thru 11 of aValue

set value of aCell to myMonth & " " & myDay & ", " & myYear

end repeat

end tell

on error

display dialog "Select some cells and try again" buttons "OK"

end try

--end of script