Thread: Date Format
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
joeu2004[_2_] joeu2004[_2_] is offline
external usenet poster
 
Posts: 829
Default 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.