Puzzled by a simple forumla to sum times based on date criteria

[lsteinbach]

I can figure out how to write VB, but figuring out how the formulas work is way more frustrating. I'm pretty sure you excel gurus can answer this in a heartbeat.
Sum all the time durations in column D if the date in column AA matches the date in cell AL1 (both dates in mm/dd/yy format).
I tried SUMIF, but keep getting 0:00 (destination cell is formatted to [h]:mm for total time).
Thank you in advance.

Luke

[Tony Valko]

What's your SUMIF formula look like? Does it look like this:

=SUMIF(AA:AA,AL1,D:D)

[lsteinbach]

Yup. What am I doing wrong?

[dilipandey]

May be :-

Please Login or Register  to view this content.

adjust the range references as per your need.


Regards,
DILIPandey
<click on below * if this helps>

[judgeh59]

did your SUMIF look anythings like this?

Please Login or Register  to view this content.

[lsteinbach]

I tried that, and it just returns 0:00 but should return 05:00. Could it be something with the date format or is it just the wrong formula altogether?

[judgeh59]

sorry, there are a few choices so I'm not sure which one didn't work....could you upload a file and I'm sure one or all of us can get it going?....please remove company sensitive data....

[Tony Valko]

Yup. What am I doing wrong?

Well, there are several possibilities.

The times to sum may not be true Excel time values.

The dates in column AA may not be true Excel date values.

The date in cell AL1 may not be a true Excel date value.

Can you post a SMALL sample file that shows us your data and the formula that is returning the incorrect result?

[lsteinbach]

example.xlsx
the file should be attached. Thanks for the help.

[judgeh59]

try this

Please Login or Register  to view this content.

[popipipo]

You use now()
The result is date and time
Column AA is just a Date
that is not equal to each other

Use Today()

[Tony Valko]

In cell AL1 you have the formula:

=NOW()-1

The NOW( ) function returns the current date plus the current time. So, this value may be something like this:

41434.6172216435

41434 is the date portion and .6172216435 is the time portion.

Column AA contains just the date so 41434 in column AA does not match 41434.6172216435 in cell AL1.

Change the formula in AL1 to:

=TODAY()-1

Do the same for the other criteria cells AN1, AM1.

=TODAY()-2
=TODAY()-3

[lsteinbach]

judgeh59 nailed it. I thought it might be a formatting issue . . . Thank you all!

[lsteinbach]

I did not know about Today(), thank you for the info.

[judgeh59]

And TODAY () might be a bit cleaner...but both work....