dates appearing as generic code when mixed with text in a formula

Any time you combine data using & (or concatenate), the result is always text.

Try this on both the small and MAX...
="aaa "&TEXT(today(),"dd/mm/yyyy")
ie
="Complaints Imported between "&text(SMALL('Data Import'!J:J,1),"dd/mm/yyyy")&................

Originally Posted by mtleigh

I have tried adding the text formula around the small and max functions but it is not working. any ideas?

What does that look like?

It should look something like this:

TEXT(SMALL('Data Import'!J:J,1),"dd/mm/yyyy")

Originally Posted by mtleigh

I tried that earlier but it didnt work

Be more specific. What does "didnt work" mean?

Did you get an incorrect result? An error? Something else?

Show us the formula you tried.

Perhaps it would help more if you could upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Are you sure you have real dates in column J?

ok so I tried ="Complaints Imported between "&SMALL('Data Import'!J:J,1)&" - "&MAX('Data Import'!J:J) (Returns dates as numbers rather than dates)
and then I tried ="Complaints Imported between "&TEXT(SMALL('Data Import'!J:J,1),"dd/mm/yyyy"&" - "&TEXT(MAX('Data Import'!J:J),"dd/mm/yyyy")) (Returns #VALUE!)

It is cell H1 where I am trying to enter the formula to ultimately display 'Complaints imported between (earliest date in the list) - (Latest date in the list)' (the date list being Column J on the data import sheet.

Good deal!