Limpieza de datos que contienen valores de fecha

Los datos que obtiene de diferentes fuentes pueden contener valores de fecha. En este capítulo, comprenderá cómo preparar sus datos que contienen valores de datos para su análisis.

Aprenderás sobre -

  • Formatos de fecha
    • Fecha en formato de serie
    • Fecha en diferentes formatos mes-día-año
  • Conversión de fechas en formato serial a formato mes-día-año
  • Conversión de fechas en formato mes-día-año a formato serial
  • Obtener la fecha de hoy
  • Encontrar un día laboral después de días especificados
  • Personalización de la definición de fin de semana
  • Número de días laborables entre dos fechas determinadas
  • Extrayendo año, mes, día de la fecha
  • Extrayendo el día de la semana de la fecha
  • Obtención de la fecha del año, mes y día
  • Cálculo del número de años, meses y días entre dos fechas

Formatos de fecha

Soporta Excel Date valores de dos maneras:

  • Formato de serie
  • En diferentes formatos año-mes-día

Puede convertir -

  • UNA Date en formato de serie a un Date en formato año-mes-día

  • UNA Date en formato año-mes-día a un Date en formato serial

Fecha en formato de serie

UNA Date en formato serial es un entero positivo que representa el número de días entre la fecha dada y el 1 de enero de 1900. Tanto el actual Datey el 1 de enero de 1900 se incluyen en el recuento. Por ejemplo, 42354 es unDate que representa el 16/12/2015.

Fecha en formatos mes-día-año

Excel admite diferentes Date Formatos basados ​​en Locale(Ubicación) tu eliges. Por lo tanto, primero debe determinar la compatibilidad de suDateformatos y el análisis de datos a mano. Tenga en cuenta que ciertosDate los formatos tienen el prefijo * (asterisco) -

  • Date los formatos que comienzan con * (asterisco) responden a los cambios en la configuración regional de fecha y hora que se especifican para el sistema operativo

  • Date los formatos sin un * (asterisco) no se ven afectados por la configuración del sistema operativo

Para fines de comprensión, puede asumir Estados Unidos como la configuración regional. Encuentra lo siguienteDate formatos a elegir para el Date- 8 º de junio de 2016 -

  • * 6/8/2016 (afectado por la configuración del sistema operativo)
  • * Miércoles 8 de junio de 2016 (afectado por la configuración del sistema operativo)
  • 6/8
  • 6/8/16
  • 06/08/16
  • 8-Jun
  • 8-Jun-16
  • 08-Jun-16
  • Jun-16
  • June-16
  • J
  • J-16
  • 6/8/2016
  • 8-Jun-2016

Si ingresa solo dos dígitos para representar un año y si:

  • Los dígitos son 30 o más, Excel asume que los dígitos representan años en el siglo XX.

  • Los dígitos son inferiores a 30, Excel asume que los dígitos representan años en el siglo XXI.

Por ejemplo, 1/1/29 se trata como el 1 de enero de 2029 y 1/1/30 se trata como el 1 de enero de 1930.

Conversión de fechas en formato serial a formato mes-día-año

Para convertir las fechas del formato de serie al formato de mes-día-año, siga los pasos que se indican a continuación:

  • Haga clic en el Number pestaña en el Format Cells caja de diálogo.

  • Hacer clic Date debajo Category.

  • Seleccione Locale. El disponibleDate Los formatos se mostrarán como una lista debajo Type.

  • Haga clic en un Format debajo Type para ver la vista previa en el cuadro adyacente a Sample.

Después de elegir el formato, haga clic en OK.

Conversión de fechas en formato mes-día-año a formato serial

Puede convertir fechas en formato mes-día-año a formato serial de dos formas:

  • Utilizando Format Cells caja de diálogo

  • Usando Excel DATEVALUE función

Usar el cuadro de diálogo Formato de celdas

  • Haga clic en el Number pestaña en el Format Cells caja de diálogo.

  • Hacer clic General debajo Category.

Uso de la función DATEVALUE de Excel

Puedes usar Excel DATEVALUE función para convertir un Date a Serial Numberformato. Necesita adjuntar elDateargumento en "". Por ejemplo,

= DATEVALUE ("6/8/2016") da como resultado 42529

Obtener la fecha de hoy

Si necesita realizar cálculos basados ​​en la fecha de hoy, simplemente use la función de Excel HOY (). El resultado refleja la fecha en que se utiliza.

La siguiente captura de pantalla de HOY uso de la función () ha sido tomada en 16 º de mayo de 2016 -

Encontrar un día laboral después de días específicos

Es posible que deba realizar ciertos cálculos en función de sus días laborales.

Los días laborables excluyen los fines de semana y festivos. Esto significa que si puede definir su fin de semana y días festivos, cualquier cálculo que haga se basará en días laborables. Por ejemplo, puede calcular las fechas de vencimiento de las facturas, los tiempos de entrega esperados, la fecha de la próxima reunión, etc.

Puedes usar Excel WORKDAY y WORKDAY.INTL funciones para tales operaciones.

S.No. Función descriptiva
1.

WORKDAY

Devuelve el número de serie de la fecha anterior o posterior a un número específico de días laborables.
2.

WORKDAY.INTL

Devuelve el número de serie de la fecha anterior o posterior a un número específico de días laborables utilizando parámetros para indicar cuáles y cuántos días son días de fin de semana.

Por ejemplo, se puede especificar la 15 ª jornada de trabajo a partir de hoy (la pantalla de abajo se toma el 16 º de mayo de 2016) utilizando las funciones de hoy y jornada laboral.

Supongamos que el 25 º de mayo de 2016 y 1 st de junio de el año 2016 son los días festivos. Entonces, su cálculo será el siguiente:

Personalización de la definición de fin de semana

Por defecto, el fin de semana es sábado y domingo, es decir, dos días. Opcionalmente, también puede definir su fin de semana con elWORKDAY.INTLfunción. Puede especificar su propio fin de semana mediante un número de fin de semana que corresponda a los días del fin de semana como se indica en la tabla siguiente. No es necesario que recuerde estos números, porque cuando comienza a escribir la función, obtiene una lista de números y los días de fin de semana en la lista desplegable.

Días de fin de semana Número de fin de semana
Sabado domingo 1 u omitido
Domingo Lunes 2
Lunes martes 3
Martes Miércoles 4
Miércoles Jueves 5
Jueves Viernes 6
Viernes sabado 7
Solo domingo 11
Solo lunes 12
Solo martes 13
Solo miércoles 14
Solo jueves 15
Solo viernes dieciséis
Solo sábado 17

Suponga que, si el fin de semana es solo viernes, debe usar el número 16 en la función WORKDAY.INTL.

Número de días laborables entre dos fechas determinadas

Puede ser necesario calcular el número de días laborables entre dos fechas, por ejemplo, en el caso de calcular el pago a un empleado contratado al que se le paga por día.

Puede encontrar el número de días laborables entre dos fechas con las funciones de Excel NETWORKDAYS y NETWORKDAYS.INTL. Al igual que en el caso de WORKDAYS y WORKDAYS.INTL, NETWORKDAYS y NETWORKDAYS.INTL le permiten especificar días festivos y con NETWORKDAYS.INTL puede especificar adicionalmente el fin de semana.

S.No. Función descriptiva
1.

NETWORKDAYS

Devuelve el número de días laborables completos entre dos fechas.
2.

NETWORKDAYS.INTL

Devuelve el número de días laborables completos entre dos fechas utilizando parámetros para indicar cuáles y cuántos son días de fin de semana.

Puede calcular el número de días laborables entre hoy y otra fecha con las funciones HOY y NETWORKDAYS. En la captura de pantalla que figura a continuación, en la actualidad es de 16 º de mayo de 2016 y la fecha final es el 16 º de junio de 2016. 25 º de mayo de 2016 y 1 st de junio de el año 2016 son los días festivos.

Nuevamente, se supone que el fin de semana es sábado y domingo. Puede tener su propia definición de fin de semana y calcular el número de días laborables entre dos fechas con la función NETWORKDAYS.INTL. En la captura de pantalla que se muestra a continuación, solo el viernes se define como fin de semana.

Extrayendo año, mes, día de la fecha

Puede extraer de cada fecha en una lista de fechas, el día, mes y año correspondientes usando las funciones de Excel DÍA, MES y AÑO.

Por ejemplo, considere las siguientes fechas:

De cada una de estas fechas, puede extraer el día, mes y año de la siguiente manera:

Extrayendo el día de la semana de la fecha

Puede extraer de cada fecha en una lista de fechas, el día de la semana correspondiente con la función de Excel WEEKDAY.

Considere el mismo ejemplo dado arriba.

Obtención de la fecha del año, mes y día

Sus datos pueden tener la información sobre el año, el mes y el día por separado. Debe obtener la fecha combinando estos tres valores para realizar cualquier cálculo. Puede utilizar la función FECHA para obtener los valores de fecha.

Considere los siguientes datos:

Utilice la función FECHA para obtener los valores FECHA.

Cálculo de años, meses y días entre dos fechas

Puede que tenga que calcular el tiempo transcurrido desde una fecha determinada. Es posible que necesite esta información en forma de años, meses y días. Un ejemplo simple sería calcular la edad actual de una persona. Efectivamente, es la diferencia entre la fecha de nacimiento y el día de hoy. Puede utilizar las funciones de Excel DATEDIF, TODAY y CONCATENATE para este propósito.

La salida es la siguiente: