Formatting dates with =TEXT()

[Idoia]

Hello,

I have the following formula (AsOfDate is a named cell with a date on it, in
case it's not clear):

="As of "&TEXT(AsOfDate,"mm/dd/yyyy")

My coworker has Excel in Spanish, so since "year" is "año" in spanish, the
formula he needs to enter is:

="As of "&TEXTO(AsOfDate;"mm/dd/aaaa")

Excel does the TEXT to TEXTO formula conversion automatically, but since it
does not recognise the yyyy part, he gets an error.

Is there a way we can both see the date correctly formatted without having
to change the formula every time we send the spreadsheet over to each other?

Thanks!

 

[Bob Phillips]

I am sure this isn't the best way, but it works

=IF(ISNUMBER(--RIGHT("As of "&TEXT(AsOfDate,"mm/dd/aaaa"),4)),"As of
"&TEXT(AsOfDate,"mm/dd/aaaa"),"As of "&TEXT(AsOfDate,"mm/dd/yyyy"))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)