Regional settings

[zzzamo]

I have got a problem that has given me a really hard time for many months.

I'm using the formula '=Text(Today(),"[$-409]mmmm-aa")' to retrieve today's date as a string, expressing the whole month and a two-digit number in English. If you see I'm using "aa" since in my regional language "aa" is the code for a two-digit year (año), and it works perfectly on all our computers down here in Mexico, but the problem is when i send an archive to someone that is residing on the US and have a regional formatting in english. It expresses the date as 'January-aa' since it does not recoginze the "aa" as the code for a two-digit year, since the code according to the english regional formatting is "yy".

I can't change the format settings to "yy", because if do so, on all the local computers here in mexico will express the date as 'January-yy'

How can I make this formula work without taking in consideration the local regional setting of the PC?

Best regards from Mexico.
EZF

 

[shg]

If you're willing to settle for a hack instead of a solution,

=TEXT(TODAY(), "[$-409]mmmm-") & TEXT(MOD(YEAR(TODAY()), 100), "00")

 

[Rick Rothstein]

If you are up to using an UDF (user defined function), then this should do what you want...

Function DateYear(Optional DateIn As Date) As String
  If DateIn = 0 Then DateIn = Date
  DateYear = Format(DateIn, String(4, Application.International(xlMonthCode)) & "-" & String(2, Application.International(xlYearCode)))
End Function

To use it, you would do this...

=DateYear()

to get today's date formatted as MonthName (note, no argument) or to get a particular date formatted that way...

=DateYear(A1)

where A1 contains a real Excel date or, alternately, when specifying the date as text...

=DateYear("4/29/2012")

If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. Go back to any worksheet and use DateYear as indicated above.

 

[zzzamo]

thanks shg, your formula woks perfectly, it makes the formula more complicated to audit thought, but works good.

Rick Rothstein, I thought about a UDF formula before, but since this archive is send to a client, is better not to include VBA, since most people, that are not used to excel, have a great tendency to ignore the warning bar telling you that you need to enable some content.

I was thinking more in a solution like changing the aa/yy value to its numeric form, for example: FALSE=0 and TRUE=1, knowing this can help you to force a Boolean input to become a numeric formula like this '=TRUE+1' equals to 2 and viceversa '=IF(1,"Yes","No")' equals "Yes"

Either way thanks for your suggestions both of you.