Excel 2013 refer to another cell to grab a date

Curtis Long
Curtis Long used Ask the Experts™
on
When I use the following equation it works great

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(2018,11,20)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(2018,11,21)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

But if I try to use this equation it breaks:

=SUMIFS(L6P!C:C,L6P!A:A,">="&(DATE(c5)+TIME(23,0,0)),L6P!A:A,"<="&(DATE(h5)+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

I am TRYING to refer to another cell to grab the date.  What am I doing wrong??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
DATE function requires 3 parameters, Year Month and Day.

Your second formula only has one parameter in each of the DATE functions. You might be able to just refer to the cell if it contains a date rather than enclosing it in a DATE function.

=SUMIFS(L6P!C:C,L6P!A:A,">="&(C5+TIME(23,0,0)),L6P!A:A,"<="&(H5+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

Where C5 and H5 contain dates 11/20/2018 and 11/21/2018

Author

Commented:
how would you change the equation to refer to just the cell instead of the date function??  This is not my equation and I am just trying to modify it to my needs.  I am not an expert in excel by any definition...:-)
Finance Analyst
Commented:
Looks like your comment was posted while I was editing my comment.

=SUMIFS(L6P!C:C,L6P!A:A,">="&(C5+TIME(23,0,0)),L6P!A:A,"<="&(H5+TIME(7,0,0)),L6P!B:B,"*l6p Graves*")

Where C5 and H5 contain dates 11/20/2018 and 11/21/2018

Author

Commented:
Perfect!!  Thank you so much!!
Rob HensonFinance Analyst

Commented:
Thanks for the feedback.

BTW, the TIME function has similar requirements; Hour, Minutes and Seconds.

If you wanted to amend the formula to refer to a cell containing times:

=SUMIFS(L6P!C:C,L6P!A:A,">="&(C5+D5),L6P!A:A,"<="&(H5+I5),L6P!B:B,"*l6p Graves*")

Where D5 and I5 contain times 23:00 and 07:00

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial