Excel - Concatenating a Date within an If

[Martynt74]

Hiya

I’m trying to do a nested if and I’ve got it working except for a Concatenate function at the end

Here is it as I have it

=IF(G2="",IF(F2-H2>0,TEXT(F2-H2,0),"OVERDUE"),("Returned - "&G2)))

However cell G2, is a date field and when I try to concatenate it it comes up as “Returned - 56599” or whatever the date is in number format

I then tried

=IF(G2="",IF(F2-H2>0,TEXT(F2-H2,0),"OVERDUE"),("Returned - "&TEXT(G2,DD/MM/YYYY)))

However this seems to bugger up the whole formula. Any chance someone could point out where I’m going wrong?

Cheers

 

[cotton31]

You need quotes around your text format, so it should be "DD/MM/YYYY".

Excel thinks you are trying to reference a cell or range which is why you are getting the #NAME error.

 

[Martynt74]

Bingo, thanks for that