56 votos

No se puede conseguir que Excel reconozca la fecha en la columna

Constantemente tengo problemas para trabajar con fechas en Excel, debo estar haciendo algo mal pero no entiendo qué.

Tengo una hoja de cálculo, exportada desde nuestro servidor de intercambio, que contiene una columna con fechas. Han salido en formato US aunque estoy en el Reino Unido.

La columna en cuestión tiene el siguiente aspecto

04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011

En Excel, he resaltado la columna y he seleccionado Format Cells... . En este cuadro de diálogo, he seleccionado el Date , seleccionado English (United States) como la configuración regional y elegir el formato de fecha correspondiente de la lista. Le doy a OK y trato de ordenar los datos por esta columna.

En el diálogo de ordenación elijo esta columna, selecciono ordenar por Valores pero el orden sólo me da opciones de la A a la Z, no de la más antigua a la más reciente como esperaría.

Esto, a su vez, ordena los datos de la fecha por los dos primeros dígitos.

Soy consciente de que podría volver a formatear estos datos a ISO y entonces la ordenación de la A a la Z funcionaría, pero no debería tener que hacerlo, obviamente me estoy perdiendo algo. ¿Qué es?

EDITAR: Me he equivocado con la recompensa pero esto debería haber ido a @r0berts respuesta, su primera sugerencia de Texto a Columnas sin delimitador y eligiendo 'MDY' como tipo de datos funciona. Además, si tiene una hora (es decir 04/21/2015 18:34:22 ), primero hay que deshacerse de los datos de la hora. Sin embargo, después de eso el método sugerido por @r0berts funciona bien.

0 votos

Sospecho que el problema es que Excel no sabe cómo hacer esto - se puede probar. Cuando intentes actualizar el formato de las fechas, selecciona la columna y haz clic con el botón derecho sobre ella y elige formatear celdas (como ya haces) pero elige la opción 2001-03-14 (cerca del final de la lista). 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!

0 votos

¿Cuál es el formato de los datos exportados? ¿Es CSV o XLSX?

90voto

r0berts Puntos 591

El problema: Excel no quiere reconocer las fechas como fechas, a pesar de que a través de "Formato de celdas - Número - Personalizado" usted está tratando explícitamente de decirle que son fechas por " mm/dd/yyyy ". Como sabes, cuando Excel reconoce algo como una fecha, lo almacena como un número - como " 41004 " pero se muestra como fecha según el formato que se especifique. Para aumentar la confusión, es posible que Excel convierta sólo una parte de las fechas, como el 04/08/2009, pero deje otras, como el 28/07/2009, sin convertir.

Solución: pasos 1 y luego 2

1) Seleccione la columna de la fecha. En Datos elija 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 en blanco ; sin marcas de verificación) y haga clic en Siguiente . En el formato de datos de la columna, seleccione Fecha y seleccione MDY en el cuadro combinado adyacente y haga clic en Acabado . Ahora tiene valores de fecha (es decir, Excel ha reconocido sus valores como Date ), pero es probable que el formato siga siendo la fecha de la configuración regional, no el mm/dd/yyyy que quieres.

2) Para que se muestre correctamente el formato de fecha deseado en EE.UU., primero hay que seleccionar la columna (si no está seleccionada) y luego en Formato de celdas - Número elija Fecha Y seleccione Locale : Inglés (US) . Esto le dará un formato como " m/d/yy ". A continuación, puede seleccionar Personalizado y allí puede escribir " mm/dd/yyyy " o elegirlo de la lista de cadenas personalizadas.

Alternativa 0 : utilizar LibreOffice Calc . Al pegar los datos del post de Patrick elija Pegado Especial ( Ctrl+Shift+V ) y seleccione Texto sin formato. Esto abrirá el cuadro de diálogo "Importar texto". El juego de caracteres sigue siendo Unicode, pero para el idioma elija Inglés (EE.UU.); también debe marcar la casilla "Detectar números especiales". Sus fechas aparecerán inmediatamente en el formato predeterminado de EE.UU. y se podrán clasificar por fechas. Si desea el formato especial de EE.UU. MM/DD/AAAA, debe especificarlo una vez a través de "Formato de celdas", ya sea antes o después de pegar.

Uno podría decir - Excel debería haber reconocido las fechas tan pronto como le dije a través de "Formato de celda" y No podría estar más de acuerdo . Desafortunadamente, sólo a través del paso 1 de arriba he sido capaz de hacer que Excel reconozca estas cadenas de texto como fechas. Obviamente, si usted hace esto mucho es un dolor en el cuello y usted podría poner juntos una rutina básica visual que haría esto para usted en un empuje de un botón. Puede ser tan simple como este código VBA en Excel:

Sub RemoveApostrophe()

For Each CurrentCell In Selection
If CurrentCell.HasFormula = False Then    
CurrentCell.Formula = CurrentCell.Value
End If
Next

End Sub

Alternativa 1: Datos | Texto a columnas

Actualización de apóstrofe inicial después de pegar: Puede ver en la barra de fórmulas que en la celda en la que no se ha reconocido la fecha hay un apóstrofe inicial. Eso significa que en la celda formateada como un número (o una fecha) hay una cadena de texto que el programa piensa - que quiere conservar como una cadena de texto. Se podría decir que el apóstrofe inicial impide que la hoja de cálculo reconozca el número. Tienes que saber buscar esto en la barra de fórmulas - porque la hoja de cálculo simplemente muestra lo que parece un número alineado a la izquierda. Para solucionar esto, seleccione la columna que desea corregir, elija en el menú Data | Text to Columns y haga clic en Aceptar. A veces podrá especificar el tipo de datos, pero si ha configurado previamente el formato de la columna para que sea su tipo de datos particular, no lo necesitará. El comando está realmente pensado para dividir una columna de texto en dos o más usando un delimitador, pero funciona como un encanto para este problema también. Lo he probado en Libreoffice pero existe la misma opción de menú en Excel también.

Alternativa 2: Editar el reemplazo en Libreoffice

Esta es la forma más rápida y mejor hasta ahora, pero que no funciona en MsOffice hasta donde yo sé. Libreoffice Calc tiene la opción de buscar/reemplazar usando Regexps (aka. Expresiones regulares) - lo que hace es buscar la celda y reemplazarla por ella misma y en el proceso Calc vuelve a reconocer el número como número y se deshace del apóstrofe inicial. Funciona muy rápido. Selecciona tu columna. Ctrl-H abre el diálogo de búsqueda y sustitución. Marque "Selección actual" y "Usar expresiones regulares". En el cuadro de búsqueda introduzca ^[0-9] - lo que significa "encontrar cualquier celda que tenga un dígito de 0 a 9 en su primera posición . En el cuadro de sustitución, introduzca & - que para libreoffice significa 'para el reemplazo utilice la cadena que encontró en el cuadro de búsqueda' . Haga clic en Replace All - y sus valores son reconocidos como números que son. Lo mejor es que funciona en las celdas que contienen sólo números con un apóstrofe inicial, nada más - es decir, no tocará las celdas que contienen apóstrofe-un espacio (o dos)-entonces número, o celdas que contienen O mayúscula en lugar de cero o cualquier otra anomalía que quieras corregir a mano.

0 votos

Abordé tus dos puntos en mi OP. Dividir los valores de fecha en columnas (y luego volver a integrarlos en formato ISO) es, por supuesto, una posibilidad, pero dado que Excel tiene amplias funciones de fecha incorporadas, me gustaría poder aprovecharlas con los datos de fecha válidos que tengo. Tu segunda opción es exactamente como describo lo que no me funciona.

0 votos

Querido Patrick, por favor, lee con atención. O puedes usar Libre Office Calc - con tus datos esto funciona directamente - simplemente preformateas la columna para Fecha - Inglés (USA) y al pegar haces "Pegado especial" "Texto sin formato" - sólo dile al cuadro de diálogo que el idioma es Inglés(USA) y no olvides marcar la casilla para reconocer los números especiales. En mi respuesta traté de insinuar lo más posible que Excel es inconsistente con esto (buggy) y tienes que recurrir a estos pasos 1 y luego 2 para lograr el resultado que quieres.

0 votos

Efectivamente, debería haber leído bien su pregunta. Tu primera sugerencia sí funciona. Debería haber tenido la recompensa. Gracias por su ayuda.

2voto

Chainsaw Puntos 78

Parece que Excel no reconoce tus fechas como fechas, las reconoce como texto, de ahí que te aparezcan las opciones de ordenación como A a Z. Si lo haces correctamente, deberías obtener algo así:

http://i.stack.imgur.com/Qb4Pj.png

Por lo tanto, es importante asegurarse de que Excel reconoce la fecha. La forma más sencilla de hacerlo es utilizar el atajo de teclado CTRL+SHIFT+3 .

Esto es lo que hice con tus datos. Simplemente los copié de tu post anterior y los pegué en excel. Luego apliqué el atajo de arriba, y obtuve la opción de ordenación requerida. Mira la imagen.

http://i.stack.imgur.com/yAa6a.png

2 votos

Lamentablemente ese atajo no me funciona.

0 votos

Qué hace el acceso directo en su sistema Patrick.

3 votos

El acceso directo no parece hacer nada en absoluto. Tengo varios sistemas a mi disposición con Office 2007, 2010 y 2013 y ninguno de ellos reacciona al atajo CTRL SHIFT 3. CTRL 1 abre correctamente el cuadro de diálogo "formatear celda".

2voto

Dave Puntos 18513

Sospecho que el problema es que Excel no es capaz de entender el formato... Aunque se selecciona el formato de fecha, se mantiene como texto.

Puedes comprobarlo fácilmente: Cuando intentes actualizar el formato de las fechas, selecciona la columna y haz clic con el botón derecho sobre ella y elige formatear celdas (como ya haces) pero elige la opción 2001-03-14 (cerca del final 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 (¡nótese los diferentes formatos en la siguiente captura de pantalla!)

enter image description here

Hay soluciones para esto, pero, ninguna de ellas será automatizada simplemente porque usted está exportando cada vez. Yo sugeriría usar VBa, donde simplemente se puede ejecutar una 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.

Otra posibilidad es crear un Excel que lea las hojas de Excel recién creadas y exportadas (desde exchange) y luego ejecutar el VBa para que te actualice el formato de fecha. Asumiré que el archivo Excel exportado de Exchange siempre tendrá el mismo nombre/directorio de archivo y proporcionaré un ejemplo de trabajo:

Por lo tanto, cree una nueva hoja de Excel, llamada ImportedData.xlsm (excel habilitado). Este es el archivo en el que importaremos el archivo Excel de Exchange exportado.

Añada 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 la fecha a aaaa-mm-dd porque así, aunque Excel te dé el filtro de ordenación A-Z en lugar de los valores más nuevos, ¡sigue funcionando!

Estoy seguro de que el código anterior tiene errores, pero obviamente no tengo ni idea de qué tipo de datos tienes, pero lo he probado con una sola columna de fechas (como la tuya) y me funciona bien.

¿Cómo puedo añadir VBA en MS Office?

0 votos

Bueno... Voy a dejar esto por un par de días para darle un poco de exposición adicional, pero esto parece una respuesta sólida. Sin embargo, esto es absolutamente loco, no puedo creer que esto sea necesario. Como es, he arreglado los datos en el origen con una pequeña función en powershell para darle la vuelta al formato ISO en el que no me importa si Excel lo reconoce como una fecha :/

1voto

JailDoctor Puntos 1

¡Lo he descubierto!

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

  1. Si utiliza la función de buscar y reemplazar y pulsa la barra espaciadora, NO funcionará.
  2. Tienes que hacer clic justo antes del número del mes, pulsar Mayúsculas y la flecha izquierda para seleccionar y copiar. A veces es necesario utilizar el ratón para seleccionar el espacio.
  3. A continuación, pegue esto como el espacio en buscar y reemplazar y todas sus fechas se convertirán en fechas.
  4. Si hay espacio y fecha Seleccione Datos>Ir a Datos>Texto a columnas>Delimitado>Espacio como separador y terminar.
  5. Se eliminarán todos los espacios.

0 votos

La razón por la que la función de buscar y reemplazar no funciona con la barra espaciadora es que lo más probable es que el hueco sea una pestaña.

0voto

Louis Puntos 121

Si Excel se niega obstinadamente a reconocer su columna como fecha, sustitúyala por otra :

  • Añadir una nueva columna a la derecha de la antigua
  • 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 pegar values
  • Ahora puede eliminar la antigua columna.

0 votos

He probado esto, se comporta igual con la nueva columna. Ver la respuesta de Dave, la tapa de la pantalla en la parte superior es lo que obtengo no importa cuántas veces me muevo entre las columnas.

0 votos

Intente forzar el problema utilizando la función DATEVALUE(columna antigua) en la fórmula de la nueva columna y, a continuación, utilice la lista desplegable de formato numérico de la barra de la cinta de opciones Inicio para elegir Fecha corta/larga.

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by: