sábado, noviembre 18, 2006

Ingresar fechas en Excel sin separadores

A veces tenemos que entrar manualmente una larga lista de fechas en Excel. Para que Excel reconozca las entradas como fechas, tenemos que usar los separadores de fechas aceptados por Excel ("/", "-" o "." dependiendo de las definiciones del sistema).
Cómo hacemos para escribir fechas sin usar separadores, lo que nos permite agilizar el trabajo?

La idea es que entramos el número 18112006 en una celda y que Excel lo convierta en la fecha 18/11/2006 (día/mes/año).

Veamos las distintas posibilidades.

1 – Usando fórmulas

En este tipo de solución entramos el número en una celda y lo transformamos en fecha en una celda contigua.




En esta tabla, entramos los números en la columna A. En la columna B escribimos la fórmula
=SI(LARGO(A2)=8,FECHA(DERECHA(A2,4),EXTRAE(A2,3,2),IZQUIERDA(A2,2)),FECHA(DERECHA(A2,4),EXTRAE(A2,2,2),IZQUIERDA(A2,1)))

La fórmula básica es FECHA(DERECHA(A2,4),EXTRAE(A2,2,2),IZQUIERDA(A2,1))

Pero dado que el número del cual partimos puede tener 7 u 8 cifras, usamos la función SI.

La ventaja de este método es que los resultados de la fórmula son interpretados por Excel como fechas. Esto nos permite ordenar la lista en orden descendiente o ascendiente de acuerdo a las fechas.
La desventaja es que debemos agregar una columna a nuestro modelo.
También conviene notar que luego de las primeras cuatro entradas, Excel copia la fórmula en la columna B automáticamente.
Otra posibilidad es utilizar la funcionalidad Formulario del menú Datos, después de haber escrito la primer línea



Al usar Formulario Excel no sólo copiará las fórmulas, sino también los formatos.

2 – Usando formato personalizado

En el menú de formato de celdas elegimos la opción Personalizada. En la ventanilla Tipo anotamos lo siguiente:

##\/##\/####




El símbolo "\" lo usamos para forzar a Excel a exhibir "/" como separador en el formato.




Como pueden ver, en la barra de fórmulas aparece el número tal como lo entramos, 21102006, pero Excel lo exhibe en la celda de acuerdo al formato personalizado.

La ventaja de este método es que no hay necesidad de agregar una columna auxiliar a nuestro modelo.
La desventaja es que no podemos ordenar nuestra lista en orden creciente (o decreciente) de acuerdo a la fecha, ya que Excel no convierte nuestras entradas en fechas, como en el método anterior.

3 – Usando Eventos (macros).

Hasta ahora he tocado sólo tangencialmente el tema de las macros en este blog. Pero si queremos un método que reúna las ventajas de los dos anteriores, tenemos que echar mano a las macros (Vba).
Eventos son un tipo especial de macros que, como su nombre lo indica, entran en acción cuando sucede algún evento en el objeto al que se refieren.
En nuestro caso usaremos el evento Worksheet_Change ligado a la hoja de cálculos específica (que hemos llamado Evento).
Para acceder a este módulo apuntamos a la pestaña con el nombre de la hoja y con un clic del botón derecho del mouse abrimos el menú, donde seleccionamos Ver Código.
Otra posibilidad es abrir el editor de Vba (Alt+F11) y activar la hoja correspondiente en el panel Vba Project:



En el módulo de VBa copiamos esta rutina


Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range

Set rngData = Range("A2:A65536")

On Error Resume Next

If Union(Target, rngData).Address = rngData.Address Then

Application.EnableEvents = False

Target.ClearFormats

Select Case Len(Target)
Case 8
Target = DateSerial(Right(Target, 4), Mid(Target, 3, 2), Left(Target, 2))
Case 7
Target = DateSerial(Right(Target, 4), Mid(Target, 2, 2), Left(Target, 1))
Case Else
MsgBox "Entrada Incorrecta"
End Select

Application.EnableEvents = True

End If


On Error GoTo 0

End Sub


Este rutina hace que cada vez que escribimos algo en una celda del rango A2:A65536 (dejamos la celda A1 disponible para el encabezamiento de la columna), Excel evalúe si es un número de 7 u 8 cifras. De serlo, lo convierte en fecha tomando los últimas cuatro cifras par el año, las dos del medio para el mes y la primera o las dos primeras para el día.
La ventaja de este método es que usamos sólo una columna y que tenemos un control cuando ingresamos un número incorrecto.
La desventaja es que se requiere ciertos conocimientos de macros (Vba) para implementarlo.




Categorías: Varios_

Technorati Tags:

16 comentarios:

  1. Gracias, por los consejos, yo utlizo mucho desde que pasamos de siglo el formato 010108, como sería en este caso? se queremos que lo cambie al formato estandar con el año de cuatro cifras.

    ResponderBorrar
  2. Hola
    en ese caso tendrías que usar la fórmula, ya que el formato personalizado no funcionaría.
    La macro habría que adaptarla.

    ResponderBorrar
  3. Como andas Jorge! Te hago una consulta, porque en el excel del laburo las fechas las tengo que meter con el formato de USA... porque seria eso?
    Es una configuracion de Excel, pero donde está?
    Tengo excel 2003... y si bien pongo que el formato (de celda) sea Fecha (español) para que salga por ejemplo: 20/09/2009, cuando ingreso los dígitos dentro de la celda, tengo que poner: 09/20/2009 para que lo pase a 20/09/2009 me explico?

    Espero puedas leerme...
    Abrazos.

    ResponderBorrar
  4. En Formato de Celdas, elegí la categoría Fecha; en Configuración Regional elegí Inglés(Estados Unidos) y en Tipo elegís el formato relevante.

    ResponderBorrar
  5. Buenos d�as:
    C�mo puedo hacer en Excel 2010, para escribir nom�s el dia y el mes (ejemplo 0101) y que Excel ponga en formato fecha (01-01-2011), es decir que agregue el a�o en forma predeterminada. Si pudiera hacerlo usando una sola columna ser�a excelente, pero aunque use dos columnas tambi�n me servir�a. Si me pudiera ayudar, desde ya muchas gracias.-

    ResponderBorrar
  6. ¿Qué determina el año? Es decir, es siempre 2011 o puede cambiar.
    Se puede hacer, por ejemplo, con una columna auxiliar con la fórmula
    =FECHA(2011;DERECHA(C2;2);IZQUIERDA(C2;2))
    suponiendo que el texto 0101 est{e en la celda C2

    ResponderBorrar
  7. Ya se que tiene tiempo es tema, pero yo tengo un problemilla que la verdad no doy con la solución y es el caso de que cuando pones fecha el sistema de lo da todo en minúsculas y lo requiere tipo titulo,

    ResponderBorrar
  8. Para eso Excel cuenta con la función MAYUSC.

    ResponderBorrar
  9. y como sería a la inversa?, por ej si quiero transformar el 21/03/2012 a 21032012??... es que si le cambio el formato (ya sea numero o general) a la celda en donde tengo la fecha se cambian numeros muy extraños! por ej el 21/03/2012 se cambia a 40989 !!!!!!!!!!!!!

    ResponderBorrar
  10. Para entender por qué la fecha 21/03/2012 se transforma en 40989 al aplicar formato general te sugiero leer esta nota.
    Para convertir la fecha al texto 21032012 se puede usar

    =DIA(A1)&MES(A1)&AÑO(A1)

    donde A1 es la celda que contiene la fecha

    ResponderBorrar
  11. Hola,

    Porque cuando inserto la fecha, me cambia el formato de la celda... es decir la celda estaba centrada y con bordes y la cambia a justificada a la derecha y sin bordes

    ResponderBorrar
  12. Hola David, porque por definición, si la celda tiene formato General, Excel convierte a formato de fechas los valores que se ven como fechas. Por ejemplo, si ingresas 15/05/2016 en una celda con formato General (el formato por definición), Excel aplicará automáticamente el formato de fecha. Pero si defines la celda previamente como Número, al ingresar la fecha veras el número 42505 (que es el número de serie de la fecha en cuestión).
    En cuanto a los bordes, ese formato no debería cambiar, por lo que supongo que estás relaizando alguno otra acción.

    ResponderBorrar
  13. Buenas Tardes Sr. Dunkelman si lo que necesito es hacer lo contrario. Me explico convertir un numero que tiene formato dd/mm/aaaa y deseo convertirlo a aaaammdd ejemplo: 01/06/1971 para que me quede 19710601. Que debo hacer? Mil gracias por tiempo.

    ResponderBorrar
  14. Todo lo que tienes que hacer es usar el formato personalizado yyyymmdd, o aaaammdd, dependiendo de las definiciones de tu sistema.

    ResponderBorrar
  15. Como puedo hacer si tengo la fecha dd/mm/aa y lo quiero convertir a ddmm ? Que me incluya el cero. Por ejemplo de tener "01/08/17" pasarlo a tener en otra celda como "0108". Gracias

    ResponderBorrar
  16. Tal como pongo en el comentario anterior al tuyo, solo que hay que usar el formato personalizado ddmm.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.