Excel 2010 Date

Is there a way to set an equation so it will look for its date ranges from another cell??

I have attached a sample sheet.

In Column E Row 2 I would like to have an equation that would say something like the following:


And have the dates change based on Cell B2 and D2.

So if I change B2 to 5-31 it would change the date in the equation to:


I also recognize that my SUMIFS equation is not exactly right.  May need a little help with it as well.  :-)

Does this make sense??

Curtis LongAsked:
Who is Participating?
barry houdiniConnect With a Mentor Commented:
Sounds like your dates are formatted as text in which case they won't be recognised as dates so zero is the result I'd expect

Try this to convert to real dates:

Select column of data

Data > Text to columns > Finish

Now your formula should work......

regards, barry
barry houdiniCommented:
You don't seem to have a sheet called Weight on the attachment.....

....but in general you can use a formula like this


You need to use & to concatenate the cell value to the "<=" or ">="

That formula will sum all values in column A where the dates in col B are between B2 and D2 inclusive)

regards, barry
Curtis LongAuthor Commented:
That works Great!!  Thanks so much!!

One other question.  I can repost as another question if you would like.

My date column is pulled from SQL  It shows up as a "General" format.

Even after I change the format the equation still shows as Zero for its answer.

Column A data looks like this:

2013-04-01 Thru 2013-06-08

My equation looks like this:

=SUMIFS('2012 Crop Year'!J:J,'2012 Crop Year'!A:A,">="&B7,'2012 Crop Year'!A:A,"<="&D7)

My Qualifier boxes have dates like this:




I have MUCH data between those dates.

Any thoughts??

Curtis LongAuthor Commented:
Thanks!!  :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.