Date Format
Errata.... I wrote:
=IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)),
DATE(DAY(A1),MONTH(A1),1))
[....]
Note-2: The MID&1&LEFT expression assumes that your long-date
form is month day, year. See your Regional and Lanaguage
Options control panel settings, and change the formula
according[ly] if necessary.
I would prefer to use an expression using the DATE function.
But I discovered that DATE(13,1,1) is (mis)interpreted as
1/1/1913 even though 1/1/13 is interpreted as 1/1/2013.
Wasn't thinking clearly. The fluke with DATE(13,...) caught me by surprise.
But it applies to the value-if-false expression as well. I guess we must
write:
=IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)),
--(MONTH(A1) & "/1/" & DAY(A1))
And that assumes that your short-date form is month/day/year.
PS: I am unhappy with this method. Hopefully someone will think of
something that works independent of regional settings.
|