Créditos: visto a Hazem Hassan (MVP Excel) en LinkedIn
Disponemos de unos datos en un rango de celdas que contienen la fecha y la hora.
El objetivo es obtener la fecha y la hora por separado de cada registro.
Podríamos simplemente referenciar las celdas de la columna B desde las columnas C y D con los formatos de Fecha corta y hora larga respectivamente.
Pero ¿porqué hacerlo fácil pudiéndolo hacer complicado? Pues eso, lo que vamos a intentar con funciones únicas que rellenen las 2 columnas
Opción 1: utilizar una función TEXTO
Esa función requiere un primer argumento valor y un segundo argumento formato.
- Como valor tomaremos el rango B3:B5
- Como formato utilizaremos una matriz de constantes para establecer 2 formatos distintos. Primero un formato de dd/mm/aaaa y luego otro de hh:mm:ss. Los separaremos por \ para que aparezcan en distintas columnas
Opción 2: jugar con la función TRUNCAR y los formatos de las celdas.
Los formatos de las celdas de la columna C de formatean como Fecha corta y las de las columna D como Hora larga.
La función TRUNCAR devuelve una parte de un valor quitando lo que no se desea. Dispone de 2 argumentos: numero y [num_decimales]. Si no se indica el segundo argumento, se asume 0.
Esta función TRUNCAR no hay que confundirla con ENTERO o con REDONDEAR. Se entiende mejor con un ejemplo para ver como, sobre el mismo dato, devuelve resultados distintos.
El truco se basa en tomar cada valor de la columna B y restarlo de ese mismo valor truncado pero multiplicando el resultado del truncado por 0 para la primera columna y por -1 para la segunda.
¿Y como indicamos las 2 columnas? Pues con una matriz de constantes {0\-1}
Paso a paso
- Para la primera columna
- Se toma el valor de la celda –> se obtiene un número con decimales
- Se le resta el valor truncado de la celda sin decimales multiplicado por 0 –> o sea, 0
- De esa forma la primera columna, que tiene formato de fecha corta, muestra sólo el valor de la fecha
- Para la segunda columna
- Se toma el valor de la celda –> se obtiene un número con decimales
- Se le resta el valor truncado de la celda sin decimales multiplicado por -1 –> se obtiene la parte entera multiplicada por 2 manteniendo el mismo decimal
- De esa forma la segunda columnas, que tiene formato de Hora larga, se muestra correctamente
Esta técnica es sólo visual ya que, de forma interna, ambas celdas tienen la fecha completa (y la segunda muy falsa)
Opción 3: utilizar las funciones ENTERO y RESIDUO
Sabiendo que en una fecha completa la parte entera es la fecha y la decimal son las horas, podemos utilizar las funciones ENTERO para obtener la fecha y la función RESIDUO para obtener la parte decimal. Las celdas de destino deben tener formatos de Fecha corta y hora larga, respectivamente.
Podemos hacer una función para cada columna:
=ENTERO(B3:B5)
=RESIDUO(B3:B5;1)
Para poder hacerlo con una sola función deberemos apilar ambas funciones con la función APILARH
Opción 4: usar REGEXEXTRACCION y patrones
En este caso utilizamos la función REGEXEXTRACCION que permite extraer parte de una cadena de texto mediante patrones.
Como en las otras opciones, el truco es utilizar un array de patrones para devolver 2 datos en celdas contiguas.
El primer patrón es obtener todos los dígitos consecutivos: «\d+»
El segundo patrón es obtener todos los números a partir del separador decimal: «\,[0-9]+»
Ambos patrones se separan por \ para que aparezcan en columnas. Si se desea en filas, se separan por ;
Si se te ocurre alguna técnica más, eres libre de exponerla.
0 comentarios