SUMIFS formula with date format issues

[swanseaexcel]

Afternoon all,

I'm a bit stuck with a SUMIFS formula. The data range I'm referencing contains numbers such as 20110131. My SUMIFS formula says if greater than 31/12/2010 and less than 30/03/2011, etc then return a total.

The problem I have is the data range date is a number and my greater than and less than definitions are dates so the SUMIFS doesn't work.

I'm stuck with these (i.e. the data range will always be a number and the date will always be a date) but how can I overcome this?

I've tried adding cells next to the dates for use as references in > or < and used for 31/12/2010 =right(A1,4)&mid(A1,4,2)&left(A1,2) but that doesn't return what I want.

Any suggestions?

THanks!!

[zbor]

Try DATE(right(A1,4), mid(A1,4,2), left(A1,2))

[arthurbr]

Or select the data - Menu Data - text to columns -Next - Next
In the "column data format" select Date and select YMD as format then Finish

[swanseaexcel]

Thanks both,

Arthurbr, I couldn't get this to work.

zbor that would work if I was translating the text format to a date (i.e. 20110418 to 18/04/2011) but I want to translate an actual date (18/04/2011 or 40651 displayed as a number) to 20110418.

Any ideas?

Thanks!

[zbor]

Maybe this:

=YEAR(A1)&TEXT(MONTH(A1), "00")&TEXT(DAY(A1), "00")