Cómo evitar que Excel use la configuración regional del sistema operativo para patrones de fecha en fórmulas

Hola usuario de nuestra página web, encontramos la solución a tu búsqueda, continúa leyendo y la obtendrás un poco más abajo.

Solución:

Esta pregunta es un poco antigua, pero hasta la fecha no parece tener respuesta. También he visto preguntas similares en varios sitios, pero aún no he encontrado ninguna respuesta que involucre solo funciones integradas de Excel. Sin embargo, es bastante fácil resolver esto con VBA. Ni siquiera tiene que saber cómo programar para hacer esto, porque la función requerida es muy simple.

Con su libro de trabajo abierto, simplemente presione Alt + F11 (para abrir el editor de VBA) Luego haga clic en el elemento del menú Insertar > Módulo
En el nuevo módulo VBA, ingrese lo siguiente:

Public Function FMT$(ByVal Value, ByVal strFormat)
    FMT = VBA.Format$(Value, strFormat)
End Function

Lo que esto hace es simplemente usar el propio VBA Format función en lugar de Excel TEXT función. Esto es exactamente lo que quieres.

Esta nueva función formateará cualquier fecha (o número) de acuerdo con cualquier formato string tu específicas. Interpretará el formato string usando el estándar (en-US) notación, independientemente de la configuración regional del sistema operativo.

Para usar esto en su libro de trabajo, simplemente escriba =FMT(A1, "yyyymmdd_hhss") en lugar de =TEXT(A1, "yyyymmdd_hhss"). (Por supuesto, puede cambiar la referencia y el formato de la celda string según sea necesario.)

Por cierto, puedes nombrar la función como quieras. elegí FMT porque era corto y porque la función puede formatear tanto números como fechas. Pero puede elegir algo más descriptivo si lo desea. Solo recuerde usar el mismo nombre en su hoja de trabajo que en el código VBA.

Tenga en cuenta que las cadenas de formato VBA no son exactamente las mismas que las cadenas de formato personalizado de Excel (por ejemplo, use “n” en lugar de “m” para los minutos), pero son muy similares. Busque aquí los detalles o busque en MSDN “Función de formato (Visual Basic para aplicaciones)”. Desplácese hasta la parte inferior para ver los distintos especificadores de formato de fecha.

Método 2

Otro enfoque que también usa VBA, pero que en realidad podría ser más fácil de mantener, es el siguiente:

1. Aplique el formato de fecha que desee a una celda, utilizando el cuadro de diálogo Formato de celda normal. Esta celda puede estar en una hoja oculta si lo prefiere; no es necesario que se muestre al usuario final. Supongamos que aplicó el formato yyyymmdd_hhss a la celda $ A $ 1 (tenga en cuenta que el guión bajo debe omitirse como se muestra).
2. Añade el GetFormat función (que se muestra a continuación) a un módulo VBA en su libro de trabajo.
3. Ingresar =GetFormat($A$1, TRUE) en otra celda (p. ej. $ B $ 1)
4. Esa función devolverá el localizado versión del formato string. Entonces, aunque originalmente formateaste $ A $ 1 con yyyymmdd_hhss, cuando abre el libro de trabajo en una computadora usando el idioma francés (por ejemplo), la función mostrará aaaammjj_hhss.
5. Ahora simplemente haga referencia a la segunda celda en todos sus TEXT funciones. Por ejemplo: =TEXT(F22, $B$1).

Aquí está el código de VBA:

Public Function GetFormat$(Cell As Range, Optional ByVal UseLocal As Boolean)
    If UseLocal Then
        GetFormat = Cell.NumberFormatLocal
    Else
        GetFormat = Cell.NumberFormat
    End If
End Function

Esto nos permite “inspeccionar” la celda que formateó originalmente ($ A $ 1) para recuperar el formato localizado.string. Ese string representará el mismo formato que aplicó, pero usará las letras correctas para que TEXT las interprete (“j” en lugar de “d”, por ejemplo), por lo que el valor mostrado de las fechas será constante en todas las configuraciones regionales. Si quisiera usar solo un formato de fecha para todo su libro de trabajo, solo necesitaría realizar los pasos 1 a 4 una vez. Luego repita el paso 5 (la función TEXTO) en todas las celdas donde lo usa actualmente, pero en lugar de codificar un formato, simplemente haría referencia a la celda que contiene el formato localizado-string ($ B $ 1 en las instrucciones de ejemplo).

Tenga en cuenta que el segundo argumento para GetFormat le dice a la función si debe o no devolver la versión localizada (que depende de la configuración regional) o la versión “estándar” (que siempre se basa en en-US).

Aquí hay algunas capturas de pantalla que podrían aclarar esto.

• En la figura, la columna 1 enumera varias representaciones de una sola fecha con diferentes formatos aplicados.
• Tenga en cuenta que las filas 2 y 3 utilizan los formatos de fecha “predeterminados del sistema” de Excel. (Estos se indican con un asterisco inicial en el cuadro de diálogo de formato e indican que se debe usar el formato de fecha predeterminado del usuario). También tenga en cuenta que la fila 5 usa un LCID entre corchetes, lo que obliga a que el idioma utilizado para los nombres de los meses y días sea el inglés ( se pueden utilizar diferentes LCID para especificar otros idiomas).
• La segunda columna muestra el resultado de GetFormat(Cell, FALSE) para cada celda de la columna 1. (Recuerde que FALSE para el segundo parámetro hace que la función devuelva el NO localizado formatos).
• La tercera columna muestra lo que GetFormat(Cell, TRUE) devuelve para cada celda en la columna 2 (es decir, el localizado formatos).
• La cuarta columna muestra el resultado de la función TEXTO utilizando el valor de fecha sin procesar original y el localizado resultado de GetFormat para reproducir el formato que se muestra en la columna 1. Sin embargo, tenga en cuenta que NO se aplicó formato de número a esta columna. Los valores son un resultado directo de la función TEXTO.

Los resultados para el caso de inglés (EE. UU.) Anterior no son muy interesantes, pero puede compararlos con los siguientes resultados que se obtuvieron al cambiar la configuración regional de mi sistema operativo a varias otras configuraciones regionales. Es importante destacar que al usar GetFormat en combinación con TEXT podemos retener un resultado constante para formatos numéricos (aquellos que no incluyen nombres de días o meses) en todas las configuraciones regionales. Y al restringir el idioma usando un LCID (como en la fila 5), ​​incluso podemos mantener un formato constante al incluir también los nombres de días y meses.

Este método funciona para la mayoría de las configuraciones regionales, sin embargo, debe tenerse en cuenta que las escrituras utilizadas para representar los números hindúes-arábigos NO son las mismas en todas las configuraciones regionales. Por lo tanto, las configuraciones regionales como las de la configuración regional de Nepal (India) darán como resultado formatos de fecha que “se ven” diferentes a las fechas en-EE. UU. Pero estos en realidad están en el mismo “formato” en términos de las posiciones de los números, solo usan diferentes símbolos para los números.

Para las fechas, puede definir un formato que haga que Excel use una determinada configuración regional. Si configura el formato de número de la celda que contiene su fecha en algo como

[$-409]m/d/yy h:mm AM/PM;@

Excel mostrará su fecha en una configuración regional de EE. UU. (Hexadecimal 409)

Gotcha: La notación YMD está localizada y, por lo tanto, se convierte en JMT en un sistema alemán, por ejemplo. Por lo que puede que tengas que adaptar el formato. string a su situación, pero esperaría que se comporte como las fórmulas (autotranslate) en sistemas que usan un lenguaje diferente (como parece que sabe).

Consulte aquí para obtener más detalles: https://stackoverflow.com/questions/894805/excel-number-format-what-is-409

y aquí para obtener una lista de configuraciones regionales: http://support.microsoft.com/kb/221435

Aunque esta pregunta es un poco antigua, encontré otra solución (¡simple!) Y la estoy publicando aquí en aras de la integridad / referencia.

• Intenté la respuesta de ricovox con éxito, pero quería una solución sin VBA.

• También probé la solución de MarinD. Pero, como se señaló en los comentarios, si necesita atender más de un formato específico de un país, puede volverse difícil de manejar bastante rápido.

• Y ahora a mi solución: usando las funciones nativas YEAR (), MONTH () y DATE () de Excel, envueltas en TEXT () para rellenar los ceros al frente. Con la fecha en A1:

  =TEXT(YEAR(A1),"0000")&"-"&TEXT(MONTH(A1),"00")&"-"&TEXT(DAY(A1),"00")

  me da la fecha en YYYY-MM-DD formato, independientemente de si la configuración regional actual requiere YYYY-MM-DD o AAAA-MM-JJ o cualquier otro nombre localizado para año / mes / fecha.

  Excel traduce sus fórmulas nativas automáticamente.

Aquí puedes ver las comentarios y valoraciones de los usuarios

Recuerda algo, que tienes autorización de añadir una tasación correcta si te fue de ayuda.

---

---

Utiliza Nuestro Buscador