Explain date formula

**ammarkhan** · 04-24-2012, 01:43 PM

Hello fellow excel users,

I had a quick question. What does this formula mean?

=IF(ISTEXT(A2),REPLACE(MID(A2,4,3)&A2,7,3,"")+0,DATE(YEAR(A2),DAY(A2),MONTH(A2)))

I get the gist but can someone break it down for me please?

Appreciate the help!

**Søren Larsen** · 04-24-2012, 02:04 PM

On a quick glance, it would seem that you have cells where data is input in 2 different ways:

1) Excel recognizes the cell content as a date; which means that it's a serial number, causing the =ISTEXT() part to return FALSE, and thus enabling you to use the =DATE() formula. (Which might actually be redundant; try to replace DATE(YEAR(A2),DAY(A2),MONTH(A2)) with A2)
2) Excel does not recognize cell contents as date, causing it to be treated as text; and thus the =ISTEXT() returns true. The =REPLACE() part then manipulates cell contents in some way.

**ammarkhan** · 04-25-2012, 04:45 AM

Thank you Larsen.

I get that bit. It is just the individual parts. This code converts American dates to European dates and I don't quite get why and how.