Sumifs not returning correct value for a date range?

Question

Level 1

8 points

Sumifs not returning correct value for a date range?

Hi! I have recently learned (sort of) how to use this function. I am using it to calculate the flying hours I have logged during certain periods, e.g: january 2011, or specific dates ranges.

The formula will work correctly if I input cells B9 and B11 as the same value (the same date), and the value returned in cell B6 is correct...

however when I put in different values in cells B9 and B11 (a date range), the value returned in B6 is incorrect!

I am expecting a value of "1.9" in one example, however the value returned is "2.6"

~And yes, I have rechecked the values so it's not just my bad maths!

I am thinking my date formats are the problem maybe?

the sheet named "Logbook Entries" has the same format as cells B9 & B11 as per the screenshot. These "dates" are not actually in the date format I believe as they appear as "Text" as per the second screeenshot. I should have thought to input them as proper dates back when I first started this spreadsheet in 2011..

I can't work out how to convert them now though if this is the problem?

(there are nearly 1000 entries so would prefer not to manually re-enter them again as dates!)

Any help would be greatly appreciated!

Thanks

MacBook Air, OS X Mavericks (10.9.2)

Posted on Apr 14, 2014 3:36 PM

Reply

Answer 1

Apr 14, 2014 3:41 PM in response to michaellusty

Where is the data? What is the formula?

Jerry

Reply

Answer 2

michaellusty Author

Level 1

8 points

Apr 14, 2014 3:46 PM in response to Jerrold Green1

the data is in the sheet named "logbook entries", the formula is in the screenshot I took and here it is again:

SUMIFS(Logbook Entries::Table 1::G,Date,">="&B9,Date,"<="&B11)

Thanks

Reply

Answer 3

michaellusty Author

Level 1

8 points

Apr 14, 2014 3:50 PM in response to Jerrold Green1

So this is what it looks like on that sheet:

And as you can see, the hours flown on the 18th and 19th of May total 1.9hrs, not 2.6...

Reply

Answer 4

Apr 14, 2014 6:28 PM in response to michaellusty

Michael,

How about sorting the Date column to see if there are any additional entries matching the criteria hiding elsewhere in the list?

Jerry

Reply

Answer 5

michaellusty Author

Level 1

8 points

Apr 14, 2014 6:48 PM in response to Jerrold Green1

Ok tried that, no replicates elsewhere in the data

Reply

Answer 6

Apr 14, 2014 6:52 PM in response to michaellusty

Would you mind emailling me the file? My address is in my profile.

Jerry

Reply

Answer 7

Apr 14, 2014 7:20 PM in response to michaellusty

Michael,

Thanks for sending the email. The document you sent me is working properly. Try opening the attachment you sent and see if it works on your end. I fixed nothing, it just worked here as you sent it...

Go figure,

Jerry

Reply

Answer 8

michaellusty Author

Level 1

8 points

Apr 14, 2014 7:30 PM in response to Jerrold Green1

How weird, I get a different returned result to you, some date ranges seem to be working fine and others not... and you have a different result to me.. Wonder what could cause this?

Reply

Answer 9

Apr 14, 2014 7:55 PM in response to michaellusty

It's a corrupted document, surely. If you have other examples of what isn't working, tell me what they are and I'll check them out on the document you sent to me.

You may end up having to Copy your content to a fresh document. First though, Close everything, Restart the computer and try again.

Jerry

Reply

Answer 10

michaellusty Author

Level 1

8 points

Apr 14, 2014 8:45 PM in response to Jerrold Green1

Ok tried restarting the system. If I input a beginning date as May-17-2011 and an end date of May-30-2011, I get a return of 6.7, but when you actually go to the logbook entries and sum those dates, the actual value is 6.0. What do you get when you try those dates?

Reply

Answer 11

SGIII

Level 8

37,562 points

Apr 14, 2014 9:13 PM in response to michaellusty

Just a quick thought, but could it be the "time" part of the "date-time" string that is throwing the results off? When you look at the cells, are all the times 12:00am? Or do they vary?

SG

Reply

Answer 12

michaellusty Author

Level 1

8 points

Apr 14, 2014 10:16 PM in response to SGIII

Hi yeah thats what I mentioned initially. The "date" values are actually just text values entered in the format

"Mar-07-2011".

Would this make a difference?

Any idea how to convert them to a proper date format?

Reply

Answer 13

SGIII

Level 8

37,562 points

Apr 15, 2014 3:53 AM in response to michaellusty

On my machine I input Mar-07-2011 as text (by typing and single quote and then the value). This is what I see (lower left):

s

Then I selected the cell and formatted as Date & Time (you can choose your preferred date format):

Then I see this:

Numbers now clearly recognizes it as a date-time string.

You should be able to select the cells with "dates" and convert them this way.

If you can't, post, and I can suggest an AppleScript that may help.

SG

Reply

Answer 14

Apr 15, 2014 7:08 AM in response to michaellusty

michaellusty wrote:

Hi yeah thats what I mentioned initially. The "date" values are actually just text values entered in the format

"Mar-07-2011".

Would this make a difference?

Any idea how to convert them to a proper date format?

My motive in having you sort the Dates, before I offered to look at the file, was to ferret out any wrongly formatted cells. Of course text will not behave correctly, in sorting or in conditional tests, but I didn't pursue this because the file you sent me has properly formatted dates. If you think this could be happening anywhere else in your document it would pay to examine it closely.

Jerry

Reply

Answer 15

michaellusty Author

Level 1

8 points

Apr 15, 2014 7:47 PM in response to SGIII

Ok I tried that exactly as per what you did and my machine doesnt want to convert them! I have numbers v3.2, the latest I believe. I have even tried create custom format > type > date and time. But still no luck. Ive never used an applescript but Im sure I could work it out

Thanks

Reply