Incapaz de hacer que Excel reconozca la fecha en la columna

El problema: Excel no quiere reconocer las fechas como fechas, aunque a través de “Formato de celdas - Número - Personalizado” se intenta explícitamente decirle que son fechas por “mm/dd/yyyy”. Como sabes; cuando Excel ha reconocido algo como una fecha, lo almacena además como un número - como “41004” pero lo muestra como fecha según el formato que especifiques. Para añadir a la confusión, Excel puede convertir sólo una parte de sus fechas, como el 08/04/2009, pero dejar otras, por ejemplo, el 28/07/2009 sin convertir.

Solución: pasos 1 y luego 2

1) Seleccione la columna de la fecha. En Datos seleccione el botón Texto a Columnas. En la primera pantalla deje el botón de radio en “ delimitado” y haga clic en Siguiente. Desmarque cualquiera de las casillas de delimitación (cualquier casilla blanco ; sin marcas de verificación) y haga clic en Siguiente. Bajo el formato de datos de la columna elija Fecha y seleccione MDY en el cuadro combinado adyacente y haga clic en Finalizar. Ahora tiene valores de fecha (es decir, Excel ha reconocido sus valores como tipo de datos Date), pero es probable que el formato siga siendo la fecha local, no el mm/dd/yyyy que desea.

2) Para que el formato de fecha estadounidense deseado se muestre correctamente, primero debe seleccionar la columna (si no está seleccionada) y luego, en Formato de la célula - Número, elija Fecha Y seleccione Localidad : Inglés (US). Esto le dará un formato como “m/d/yy”. Luego puedes seleccionar Personalizado y allí puedes escribir “mm/dd/yyyy” o elegirlo de la lista de cadenas personalizadas.

Alternativo : usa LibreOffice Calc. Al pegar los datos del post de Patrick elige Pegar Especial (Ctrl+Shift+V) y elige Texto sin formato. Esto abrirá el cuadro de diálogo “Importar texto”. El juego de caracteres sigue siendo Unicode pero para el idioma elige English(USA); también deberías marcar la casilla “Detectar números especiales”. Sus fechas aparecen inmediatamente en el formato predeterminado de EE.UU. y se pueden ordenar por fecha. Si deseas el formato especial de EE.UU. MM/DD/AAAA necesitas especificarlo una vez a través de “Formato de celdas” - ya sea antes o después de pegar.

Se podría decir - Excel debería haber reconocido las fechas tan pronto como se lo dije a través de “Formato de celdas” y no podría estar más de acuerdo. Desafortunadamente es sólo a través del paso 1 desde arriba que he sido capaz de hacer que Excel reconozca estas cadenas de texto como fechas. Obviamente si haces esto mucho es un dolor de cabeza y podrías armar una rutina básica visual que lo haga por ti con sólo pulsar un botón.

Datos | Texto a Columnas

Actualizar en apóstrofe principal después de pegar: Puedes ver en la barra de fórmulas que en la celda donde no se reconoce la fecha hay un apóstrofe principal. Eso significa que en la celda formateada como número (o fecha) hay una cadena de texto. Se podría decir que el apóstrofe principal impide que la hoja de cálculo reconozca el número. Necesitas saber que debes buscar en la barra de fórmulas para esto, porque la hoja de cálculo simplemente muestra lo que parece un número alineado a la izquierda. Para solucionar este problema, selecciona la columna que quieres corregir, elige en el menú Data | Text to Columns y haz clic en OK. A veces podrás especificar el tipo de datos, pero si previamente has establecido el formato de la columna para que sea tu tipo de datos particular - no lo necesitarás. El comando realmente está pensado para dividir una columna de texto en dos o más usando un delimitador, pero también funciona como un encanto para este problema. Lo he probado en Libreoffice, pero también existe el mismo elemento de menú en Excel.

Selecciona toda la columna y ve a Localizar y Reemplazar y reemplaza "/" por /.

Esto puede no ser relevante para el interrogador original, pero puede ayudar a alguien más que no puede clasificar una columna de fechas.

Encontré que Excel no reconocería una columna de fechas que fueran todas anteriores a 1900, insistiendo en que eran una columna de texto (ya que el 1/1/1900 tiene el equivalente numérico 1, y aparentemente no se permiten los números negativos). El proceso de clasificación entonces funcionó bien. Finalmente hice un reemplazo de la otra manera, por ejemplo 190 ——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-

Estaba tratando un asunto similar con miles de filas extraídas de una base de datos SAP e, inexplicablemente, dos formatos de fecha diferentes en la misma columna de fecha (la mayoría siendo nuestro estándar “AAAA-MM-DD” pero alrededor del 10% siendo “MM-DD-YYYY”). La edición manual de 650 filas una vez al mes no era una opción.

Ninguna (cero… 0… nulo) de las opciones anteriores funcionó. Sí, las probé todas. Copiar a un archivo de texto o exportar explícitamente a txt todavía, de alguna manera, no tenía ningún efecto en el formato (o la falta del mismo) del 10% de las fechas que simplemente se sentaban en su esquina negándose a comportarse.

La única manera de arreglarlo fue insertar una columna en blanco a la derecha de la columna de la fecha que se comportaba mal y usando la siguiente fórmula, bastante simplista:

(asumiendo que sus datos de comportamiento erróneo están en Column D)

=IF(MID(D2,3,1)="-",DATEVALUE(TEXT(CONCATENATE(RIGHT(D2,4),"-",LEFT(D2,5)),"YYYY-MM-DD")),DATEVALUE(TEXT(D2,"YYYY-MM-DD")))

Podía entonces copiar los resultados en mi nueva columna calculada sobre las fechas de comportamiento erróneo pegando “Valores” sólo después de lo cual podía borrar mi columna calculada.

Tuve exactamente el mismo problema con las fechas en Excel. El formato coincidía con los requisitos para una fecha en excel, pero sin editar cada celda no reconocería la fecha, y cuando se trata de miles de filas no es práctico editar manualmente cada celda. La solución es ejecutar un hallazgo y reemplazar en la fila de fechas en la que sustituí el hypen por un guión. Excel corre a través de la columna reemplazando como con como pero el factor resultante es que ahora reconozca las fechas! ¡ARREGLADO!

Parece que Excel no reconoce las fechas como fechas, las reconoce como texto, por lo que se obtienen las opciones de clasificación de la A a la Z. Si se hace correctamente, se obtendrá algo así:

De ahí que sea importante asegurarse de que Excel reconozca la fecha. La forma más simple de hacerlo, es usar el atajo CTRL+MAYÚS+3.

Esto es lo que hice con tus datos. Simplemente los copié de tu mensaje de arriba y los pegué en Excel. Luego apliqué el atajo de arriba, y obtuve la opción de clasificación requerida. Revisa la imagen.

Sospecharía que el problema es que Excel no puede entender el formato… Aunque seleccione el formato de la fecha, permanece como texto.

Puede probar esto fácilmente: Cuando intentes actualizar el formato de las fechas, selecciona la columna y haz clic con el botón derecho del ratón sobre ella y elige el formato de las celdas (como ya lo haces) pero elige la opción 2001-03-14 (cerca de la parte inferior de la lista). Luego revisa el formato de tus celdas.

Sospecharé que sólo algunos de los formatos de fecha se actualizan correctamente, lo que indica que Excel sigue tratándolo como una cadena, no como una fecha (¡observa los diferentes formatos en la siguiente captura de pantalla!

Hay soluciones provisionales para esto, pero, ninguna de ellas será automatizada simplemente porque estás exportando cada vez. Yo sugeriría usar VBa, donde usted puede simplemente ejecutar un macro que convierte de EE.UU. a formato de fecha del Reino Unido, pero, esto significaría copiar y pegar el VBa en su hoja cada vez.

Alternativamente, usted crea un Excel que lee las hojas de Excel recién creadas y exportadas (de intercambio) y luego ejecuta el VBa para actualizar el formato de fecha para usted. Asumiré que el archivo de Excel del intercambio exportado siempre tendrá el mismo nombre de archivo/directorio y le daré un ejemplo práctico:

Entonces, cree una nueva hoja de Excel, llamada ImportedData.xlsm (excel activado). Este es el archivo en el que importaremos el archivo de Excel de Intercambio Exportado!

Añadir esta VBa al archivo ImportedData.xlsm

Sub DoTheCopyBit()

Dim dateCol As String
dateCol = "A" 'UPDATE ME TO THE COLUMN OF THE DATE COLUMN

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

directory = "C:\Users\Dave\Desktop\" 'UPDATE ME
fileName = Dir(directory & "ExportedExcel.xlsx") 'UPDATE ME (this is the Exchange exported file location)

Do While fileName <> ""

'MAKE SURE THE EXPORTED FILE IS OPEN
Workbooks.Open (directory & fileName)

Workbooks(fileName).Worksheets("Sheet1").Copy _

Workbooks(fileName).Close

fileName = Dir()

Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

Dim row As Integer
row = 1
Dim i As Integer
Do While (Range(dateCol & row).Value <> "")

     Dim splitty() As String
     splitty = Split(Range(dateCol & row).Value, "/")
     Range(dateCol & row).NumberFormat = "@"
     Range(dateCol & row).Value = splitty(2) + "/" + splitty(0) + "/" + splitty(1)
     Range(dateCol & row).NumberFormat = "yyyy-mm-dd"
     row = row + 1
Loop

End Sub

Lo que también hice fue actualizar el formato de fecha a aaaa-mm-dd porque de esta manera, incluso si Excel le da el filtro de ordenación A-Z en lugar de los valores más recientes, ¡todavía funciona!

Estoy seguro de que el código anterior tiene errores, pero obviamente no tengo ni idea de qué tipo de datos tienes, pero lo probé con una sola columna de fechas (como tú) y funciona bien para mí! ¿Cómo añado VBA en MS Office?

https://support.office.com/en-us/article/Convert-dates-stored-as-text-to-dates-8df7663e-98e6-4295-96e4-32a67ec0a680

Solución simple aquí - crear nueva columna uso =valor de la fecha (celda) fórmula y luego copiar la fórmula en sus otras filas - unos pocos segundos para arreglar

¡Lo descubrí!

Hay un espacio al principio y al final de la fecha.

1. Si usas encontrar y reemplazar y presionas la barra espaciadora, no funcionará.
2. 2. Tienes que hacer clic con el botón derecho antes del número del mes, presionar shift y la flecha izquierda para seleccionar y copiar. A veces necesitas usar el ratón para seleccionar el espacio.
3. Entonces pega esto como el espacio en encontrar y reemplazar y todas tus fechas se convertirán en fechas.
4. Si hay espacio y fecha Selecciona Data>Go to Data>Text to columns>Delimited>Space as separator and then finish.
5. Todos los espacios serán eliminados.

No he tenido suerte con todas las sugerencias anteriores… y se me ocurrió una solución muy simple que funciona como un encanto. Pega los datos en Google Sheets, resalta la columna de la fecha, haz clic en el formato, luego en el número, y vuelve a numerar para cambiar el formato de los números. Luego copio y pego los datos en la hoja de cálculo de Excel, hago clic en las fechas y luego doy formato a las celdas hasta la fecha. Muy rápido. Espero que esto ayude.

Simplemente copié el número 1 (uno) y lo multipliqué por la columna de la fecha (datos) usando la función PEGADO ESPECIAL. Luego cambia al formato general i.e 42102 Luego va a aplicar la fecha en el formato y ahora lo reconoce como una fecha.

Espero que esto ayude

Mi solución en el Reino Unido - tenía mis citas con puntos en ellas como esto:

03.01.17

Resolví esto con lo siguiente:

1. Resaltar toda la columna.
2. Ve a buscar y selecciona / reemplazar.
3. Reemplacé todos los puntos con un guión medio, por ejemplo, 03.01.17 03-01-17.
4. Mantén la columna resaltada.
5. Formatea las celdas, selecciona la fecha en la pestaña de números.
6. Usa el Tipo 14-03-12 (esencial para que el mío tenga el guión del medio)
7. Inglés local (Reino Unido)

Al ordenar la columna se ordenan todas las fechas del año.

Compartiendo una solución un poco más larga pero mucho más fácil para el sujeto….. No hay necesidad de correr macros o cosas geeky….simples fórmulas ms excel y edición.

Fechas mixtas instantánea excel:

• La fecha está en el formato mixto.
  
• Por lo tanto, para hacer un formato de fecha simétrico, se han creado columnas adicionales convirtiendo esa columna de fecha de “formato mixto” en TEXTO.
• De ese texto hemos identificado las posiciones de “/” y se ha extraído el texto del medio determinando la fecha, el mes, el año utilizando la fórmula MID.
• Aquellas que eran originalmente fechas, la fórmula terminó con ERROR / #VALUE resultado. - Finalmente, de la cadena que creamos - hemos convertido esas a fechas por la fórmula de la FECHA.
• #VALUE fueron escogidas tal cual por IFERROR añadidas a la fórmula de la FECHA y la columna fue formateada como se requiere (aquí, dd/mmm/yy)

* REFIÉRASE A LA FOTOSA DE LA FICHA DE EXCEL POR ENCIMA (= instantánea de Excel de fechas mezcladas) *

Probé las diferentes sugerencias, pero encontré que la solución más fácil para mí era la siguiente…

Ver imágenes 1 y 2 para el ejemplo… (nota - algunos campos fueron escondidos intencionalmente ya que no contribuyen al ejemplo). Espero que esto ayude…

1. Campo C - un formato mixto que me volvió absolutamente loco. Así es como los datos venían directamente de la base de datos y no tenían espacios extra o caracteres locos. Al mostrarlo como un texto por ejemplo, el 01/01/2016 se mostraba como un valor de tipo ‘42504’, mezclado con el 15/04/2006 que se mostraba tal cual.

2. Campo F - donde obtuve la longitud del campo C (la fórmula LEN sería una longitud de 5 de 10 dependiendo del formato de la fecha). El campo tiene un formato general para simplificar.

3. Campo G - donde obtengo el componente de mes de la fecha mixta - en base al resultado de la longitud en el campo F (5 ó 10), obtengo el mes a partir del valor de la fecha en el campo C, o bien obtengo los caracteres del mes en la cadena.

4. Campo H - obtención del componente día de la fecha mixta - basado en el resultado de la longitud en el campo F (5 ó 10), I o bien obtiene el día a partir del valor de la fecha en el campo C, u obtiene los caracteres día en la cadena.

Puedo hacer esto para el año también, y luego crear una fecha de los tres componentes que sea consistente. De lo contrario, puedo usar los valores individuales del día, mes y año en mi análisis. Imagen 1: hoja de cálculo básica que muestra los valores y los nombres de los campos Imagen 2: hoja de cálculo que muestra las fórmulas que coinciden con la explicación anterior

Espero que esto ayude.

Este problema me estaba volviendo loco, entonces me tropecé con una solución fácil. Al menos funcionó para mis datos. Es fácil de hacer y de recordar. Pero no sé por qué esto funciona, pero cambiar los tipos como se indica arriba no lo hace. 1. Selecciona la(s) columna(s) con las fechas 2. 3. Busca un año en tus fechas, por ejemplo 2015. 3. Selecciona Reemplazar todo con el mismo año, 2015. 3. 3. Repite el proceso para cada año. Esto cambia los tipos a fechas reales, año por año. Esto sigue siendo engorroso si tienes muchos años en tus datos. Más rápido es buscar y reemplazar 201 con 201 (para reemplazar de 2010 a 2019); reemplazar 200 con 200 (para reemplazar de 2000 a 2009); y así sucesivamente.

Selecciona las fechas y pasa este código por encima de ellas…

Todas las soluciones anteriores - incluyendo los r0berts - no tuvieron éxito, pero encontré esta extraña solución que resultó ser la más rápida.

Estaba tratando de ordenar las “fechas” en una hoja de cálculo descargada de las transacciones de la cuenta.

Esto había sido descargado a través del navegador CHROME. Ninguna cantidad de manipulación de las “fechas” haría que se reconocieran como clasificables de viejo a nuevo.

Pero, luego descargué a través del navegador INTERNET EXPLORER - asombroso - pude clasificar instantáneamente sin tocar una columna.

No puedo explicar por qué los diferentes navegadores afectan el formato de los datos, excepto que claramente lo hizo y fue una solución muy rápida.

Si Excel se niega obstinadamente a reconocer su columna como fecha, reemplácela por otra :

• Añada una nueva columna a la derecha de la antigua columna
• Haga clic con el botón derecho del ratón en la nueva columna y seleccione Format
• Establezca el formato en date
• Resalte toda la columna antigua y cópiela
• Resalte la celda superior de la nueva columna y seleccione Paste Special, y sólo pegue values
• Ahora puede eliminar la columna antigua.

Esto ocurre todo el tiempo cuando se intercambian datos de fechas entre locales en Excel. Si tiene control sobre el programa VBA que exporta los datos, sugiero exportar el número que representa la fecha en lugar de la fecha misma. El número se correlaciona de forma única con una sola fecha, independientemente del formato y la localidad. Por ejemplo, en lugar de que el archivo CSV muestre 24/09/2010 mostrará 40445.

En el bucle de exportación, cuando mantenga cada celda, si es una fecha, conviértala en número usando CLng(myDateValue). Este es un ejemplo de mi bucle que recorre todas las filas de una tabla y exporta a CSV (nota que también reemplazo las comas en las cadenas con una etiqueta que quito cuando importo, pero puede que no necesites esto):

arr = Worksheets(strSheetName).Range(strTableName & "[#Data]").Value

    For i = LBound(arr, 1) To UBound(arr, 1)
        strLine = ""
        For j = LBound(arr, 2) To UBound(arr, 2) - 1
            varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
            strLine = strLine & varCurrentValue & ","
        Next j
        'Last column - not adding comma
        varCurrentValue = arr(i, j)
            If VarType(varCurrentValue) = vbString Then
                varCurrentValue = Replace(varCurrentValue, ",", "<comma>")
            End If
            If VarType(varCurrentValue) = vbDate Then
                varCurrentValue = CLng(varCurrentValue)
            End If
        strLine = strLine & varCurrentValue

        strLine = Replace(strLine, vbCrLf, "<vbCrLf>") 'replace all vbCrLf with tag - to avoid new line in output file
        strLine = Replace(strLine, vbLf, "<vbLf>") 'replace all vbLf with tag - to avoid new line in output file
        Print #intFileHandle, strLine
    Next i

Yo uso un Mac.

Ve a las preferencias de Excel.

El problema probablemente comenzó cuando recibiste un archivo con un sistema de fechas diferente, y eso arruinó tu función de fechas de Excel.

Normalmente sólo creo una columna extra para propósitos de clasificación o totalización, así que usa year(a1)&if len(month(a1))=1,),"")&month(a1)&day(a1).

Eso proveerá un resultado de yyyymmdd que puede ser clasificado. Usar el len(a1) sólo permite añadir un cero extra para los meses 1-9.