| lección 2| lección 3 | lección 4 | lección 5
Creo que ya sabes por la última lección que las fechas y horas en Excel se almacenan en forma de números ordinales, cuyo punto de partida se considera un cierto 0 de enero de 1900. Afortunadamente, en las celdas no vemos estos números, sino fechas y horas que nos son familiares y que se pueden almacenar en una variedad de formatos. En esta lección, aprenderá cómo ingresar fechas y horas en Excel para obtener el formato que desea.
Ingresar fechas y horas en Excel
Las fechas y horas en Excel se pueden ingresar como un número ordinal o una fracción de día, pero como usted mismo comprende, esto no es del todo conveniente. Además, con este enfoque, tendrás que aplicar un formato de número determinado a la celda cada vez.
Excel ofrece varios formatos para ingresar datos temporales. Si usa este formato, Excel convertirá automáticamente la fecha (u hora) ingresada a un número ordinal (una fracción de un día) y aplicará el formato a la celda. fechas(o Hora) configurado de forma predeterminada.
La siguiente figura muestra una tabla de las opciones de entrada de fecha y hora que admite Excel. La columna de la izquierda muestra los valores que deben ingresarse en la celda y la columna de la derecha muestra el resultado de la conversión. Vale la pena señalar que a las fechas ingresadas sin especificar un año se les asigna el año actual, es decir, el establecido en la configuración de su Sistema operativo.
Eso no es todo opciones posibles, que admite Excel. Pero incluso estas opciones serán suficientes para ti.
Algunas opciones de visualización de fecha que se muestran en la columna de la derecha pueden variar. Esto depende de su configuración regional y del formato de visualización de fecha y hora en la configuración de su sistema operativo.
Trabajando con células en Microsoft Excel, que contienen una fecha u hora, tienes la posibilidad de aplicarles diferentes formatos. Por ejemplo, puede mostrar en una celda solo el día de la semana, o solo el mes y el año, o solo la hora.
Puedes acceder a todos los formatos posibles en la pestaña Número caja de diálogo Formato de celda. En categoría fecha Los formatos de fecha incorporados son:
Para aplicar formato a una celda, simplemente seleccione formato requerido en el capitulo Tipo y presione DE ACUERDO. Se aplicará el formato requerido. Si los formatos de números integrados no son suficientes para usted, puede usar la categoría Todos los formatos. Aquí también puedes encontrar muchas cosas interesantes.
Si ninguna de las opciones le conviene, puede crear un formato de número personalizado. Es bastante fácil de crear si utiliza los formatos de números integrados como plantilla. Para crear un formato de número personalizado, siga estos pasos:
Como puedes ver, ¡todo es bastante sencillo!
En esta lección, aprendimos cómo personalizar el formato para mostrar fechas y horas en Microsoft Excel y también analizamos varias opciones útiles para ingresarlas en una hoja de trabajo. En la próxima lección hablaremos de
Las hojas de cálculo de Excel brindan la posibilidad de trabajar con varios tipos información textual y numérica. El procesamiento de fechas también está disponible. En este caso, puede ser necesario aislar un número específico, por ejemplo, un año, del valor total. Para ello existen funciones independientes: AÑO, MES, DÍA y DÍA SEMANAL.
Ejemplos de uso de funciones para procesar fechas en Excel
Las tablas de Excel almacenan fechas, que se representan como una secuencia de valores numéricos. Comienza el 1 de enero de 1900. Esta fecha corresponderá al número 1. Además, el 1 de enero de 2009 está incluido en las tablas con el número 39813. Este es exactamente el número de días entre las dos fechas designadas.
La función AÑO se utiliza de manera similar a las relacionadas:
- MES;
- DÍA;
Todos ellos muestran valores numéricos según el calendario gregoriano. Incluso si en hoja de cálculo de Excel Para mostrar la fecha ingresada, se seleccionó el calendario Hijri, luego, al aislar el año y otros valores componentes usando funciones, la aplicación presentará un número que es equivalente en el sistema de cronología gregoriana.
Para utilizar la función AÑO, debe ingresar la siguiente fórmula de función con un argumento en una celda:
AÑO (dirección de celda con fecha en formato numérico)
El argumento de la función es obligatorio. Se puede reemplazar con "date_in_numeric_format". En los ejemplos siguientes, puedes ver esto claramente. Es importante recordar que al mostrar la fecha como texto (orientación automática hacia el borde izquierdo de la celda), no se ejecutará la función AÑO. Su resultado será la visualización #VALOR. Por lo tanto, las fechas formateadas deben presentarse en forma numérica. Los días, meses y años pueden estar separados por un punto, una barra o una coma.
Veamos un ejemplo de cómo trabajar con la función AÑO en Excel. Si necesitamos obtener el año a partir de la fecha de origen, la función DERECHA no nos ayudará ya que no funciona con fechas, sino solo con texto y valores numéricos. Para separar el año, mes o día de la fecha completa, Excel proporciona funciones para trabajar con fechas.
Ejemplo: Hay una tabla con una lista de fechas y en cada una de ellas hay que separar el valor de solo el año.
Ingresemos los datos de origen en Excel.
Para resolver el problema, debes ingresar la fórmula en las celdas de la columna B:
AÑO (dirección de la celda de cuya fecha necesita extraer el valor del año)
Como resultado, extraemos los años de cada fecha.
Un ejemplo similar de la función MES en Excel:
Un ejemplo de trabajo con las funciones DÍA y DÍA SEMANAL. La función DÍA consigue calcular el día de cualquier día a partir de una fecha:
La función DÍASEMANA devuelve el número del día de la semana (1-lunes, 2-martes... etc.) para cualquier fecha:
En el segundo argumento opcional de la función DÍA SEMANA, debes especificar el número 2 para nuestro formato para contar el día de la semana (del lunes 1 al domingo 7):
Si omite el segundo argumento opcional, se utilizará el formato predeterminado (domingo 1 en inglés a sábado 7).
Creemos una fórmula a partir de combinaciones de las funciones ÍNDICE y DÍA SEMANAL:
Consigamos una forma más comprensible de implementación de esta función.
Ejemplos de aplicación práctica de funciones para trabajar con fechas.
Estas funciones primitivas son muy útiles para agrupar datos por: años, meses, días de la semana y días específicos.
Digamos que tenemos un informe de ventas simple:
Necesitamos organizar rápidamente los datos para el análisis visual sin utilizar tablas dinámicas. Para ello, te presentamos el informe en una tabla donde podrás agrupar los datos de forma cómoda y rápida por año, mes y día de la semana:
Ahora tenemos una herramienta para trabajar con este informe de ventas. Podemos filtrar y segmentar datos en función de determinados criterios temporales:
Además, puedes crear un histograma para analizar los días de la semana más vendidos, para entender en qué día de la semana cae. mayor numero ventas:
De esta forma, resulta muy conveniente segmentar los informes de ventas para períodos de tiempo largos, medianos y cortos.
Vale la pena señalar de inmediato que ninguna de las funciones descritas anteriormente nos ayudará a obtener la diferencia entre dos fechas. Para esta tarea, debe utilizar la función RAZNDAT especialmente diseñada:
El tipo de valores en las celdas de fecha requiere un enfoque especial al procesar datos. Por lo tanto se debe utilizar el adecuado este tipo Funciones en Excel.
Las fechas suelen ser una parte importante del análisis de datos. A menudo hace preguntas como: ¿cuándo se compró el producto, cuánto tiempo llevará completar una tarea de un proyecto o cuál es el ingreso promedio para el trimestre fiscal? Introducir las fechas correctamente es esencial para garantizar resultados precisos. Sin embargo para comprensión correcta Estos resultados deben tener formato de fecha para que sean más fáciles de entender.
Importante: Debido a que las reglas que rigen cómo cualquier programa de cálculo interpreta las fechas son complejas, debe ser lo más específico posible en cada entrada. Esto garantizará que los cálculos de fechas sean lo más precisos posible.
Acerca de los cálculos y los formatos de fecha
Excel almacena fechas como números secuenciales llamados valores secuenciales. Por ejemplo, en Excel para Windows, el 1 de enero de 1900 es el número de secuencia 1 y el 1 de enero de 2008 es el número de secuencia 39448 porque son 39 448 días después del 1 de enero de 1900.
Excel almacena los valores de tiempo como decimales porque el tiempo se trata como una fracción de un día. Número decimal es un valor que va de 0 (cero) a 0,99999999, que representa el tiempo comprendido entre las 0:00:00 (12:00:00 a. m.) y las 23:59:59 (23:59:59 p. m.).
Debido a que los valores de fecha y hora son valores, se pueden sumar, restar e incluir en otros cálculos. Puede ver una fecha como un valor ordinal y una hora como decimal cambiando el formato de la celda que contiene la fecha o la hora a un formato común.
Comprender los dos sistemas de fechas
Excel para Mac y Excel para Windows admiten los sistemas de fechas 1900 y 1904. El sistema de fechas predeterminado para Excel para Windows es 1900; y el sistema de fechas predeterminado de Excel para Mac es 1904.
Excel para Windows se basó originalmente en el sistema de fechas 1900 porque mejoró la compatibilidad con otros programas de hojas de cálculo desarrollados para el sistema operativo MS-DOS y Microsoft Windows, y así se convirtió en el sistema de fechas estándar. Excel para Mac se basó originalmente en el sistema de fechas de 1904 porque mejoraba la compatibilidad con las primeras computadoras Macintosh, que no admitían fechas anteriores al 2 de enero de 1904, por lo que se convirtió en el sistema de fechas predeterminado.
La siguiente tabla muestra la primera fecha y la fecha del último período para cada sistema de fechas y el significado secuencial asociado con cada fecha.
Debido a que los dos sistemas de fechas utilizan días de inicio diferentes, la misma fecha está representada por números de serie diferentes en cada sistema de fechas. Por ejemplo, el 5 de julio de 2007 puede tener dos significados secuenciales diferentes según el sistema de fechas utilizado.
La diferencia entre los dos sistemas de fechas es de 1.462 días; Esto significa que el número de serie de una fecha en el sistema de fechas de 1900 es siempre 1462 días menor que el valor de serie de la misma fecha en el sistema de fechas de 1904. Por el contrario, el número de serie de una fecha en el sistema de fechas de 1904 es siempre 1462 días. menos que el valor de serie de las mismas fechas en el sistema de fechas de 1900, 1.462 días equivalen a 4 años y un día (incluido un día bisiesto).
Cambiando la forma en que se interpreta un año de dos dígitos
Importante: Para asegurarse de que los valores del año se interpreten correctamente, ingrese cuatro dígitos (por ejemplo, 2001 en lugar de 01). Si ingresa años de cuatro dígitos, Excel no interpretará los siglos.
Si ingresa una fecha de año de dos dígitos en una celda de texto o argumento de texto en una función, como "=año" ("1/1/31"), Excel interpreta el año de la siguiente manera:
00 – 29 se interpreta como años desde 2000 hasta 2029. Por ejemplo, al ingresar una fecha 5/28/19 Excel cree que la fecha podría ser el 28 de mayo de 2019.
de 30 a 99 interpretado como años de 1930 a 1999. Por ejemplo, al ingresar una fecha 5/28/98 Excel cree que la fecha podría ser el 28 de mayo de 1998.
En Microsoft Windows, puedes cambiar la forma en que se interpretan dos dígitos del año para todos. programas instalados Ventanas.
ventanas 10
Panel de control y luego seleccione Panel de control.
en el capitulo Reloj, lenguaje, y región haga clic.
Haga clic en el icono de Idioma y estándares regionales.
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
en el campo
Clic en el botón DE ACUERDO.
ventana 8
Buscar Buscar), ingresa en el campo de búsqueda Elemento del panel de control Panel de control.
en el capitulo Reloj, lenguaje, y región haga clic.
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
en el campo después de ingresar dos dígitos del año, interpretelo como el año entre marcos, cambiando el límite superior del párpado.
A medida que cambia el límite superior, el año cambia automáticamente al límite inferior.
Clic en el botón DE ACUERDO.
ventana 7
Clic en el botón Comenzar y seleccione Panel de control.
Seleccione un artículo.
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
en el campo después de ingresar dos dígitos del año, interpretelo como el año entre marcos, cambiando el límite superior del párpado.
A medida que cambia el límite superior, el año cambia automáticamente al límite inferior.
Clic en el botón DE ACUERDO.
Cambie el formato de fecha predeterminado para mostrar el año con cuatro dígitos
De forma predeterminada, cuando ingresa fechas en el libro, las fechas tienen formato para mostrar años de dos dígitos. Cuando cambia el formato de fecha predeterminado a otro formato usando este procedimiento, la visualización de las fechas ingresadas previamente en el libro de trabajo se cambiará al nuevo formato hasta que se formatee en el cuadro de diálogo. Formato de celda" "(en la pestaña hogar en grupo número clic en el botón llamar al cuadro de diálogo).
ventanas 10
En el cuadro de búsqueda de la barra de tareas, escriba Panel de control y luego seleccione Panel de control.
en el capitulo Reloj, lenguaje, y región hacer clic cambiar formatos de fecha, hora y número .
Haga clic en el icono de Idioma y estándares regionales.
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
En la lista corta Formato de fecha
Clic en el botón DE ACUERDO.
ventana 8
Desliza el dedo desde el borde derecho de la pantalla, toca Buscar(o si está usando un mouse, muévalo a la esquina superior derecha de la pantalla, muévalo hacia abajo y haga clic en Buscar), ingresa en el campo de búsqueda Elemento del panel de control y luego presione o Seleccionar Panel de control.
en el capitulo Reloj, lenguaje, y región hacer clic cambiar formatos de fecha, hora o número.
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
En la lista corta Formato de fecha seleccione un formato que utilice cuatro dígitos para el año (aaaa).
Clic en el botón DE ACUERDO.
ventana 7
Clic en el botón Comenzar y seleccione Panel de control.
selecciona un artículo Idioma y estándares regionales..
En el cuadro de diálogo región clic en el botón Opciones adicionales.
Abre la pestaña fecha .
En la lista corta Formato de fecha seleccione un formato que utilice cuatro dígitos para el año (aaaa).
Clic en el botón DE ACUERDO.
Cambiar el sistema de fechas en Excel
El sistema de fechas cambia automáticamente cuando abres un documento creado en otra plataforma. Por ejemplo, si está trabajando en Excel y abre un documento creado en Excel para Mac, el " sistema de fecha 1904" se instala automáticamente.
Puede cambiar el sistema de fechas siguiendo los pasos a continuación.
Seleccionar Archivo > Opciones > Además.
en el capitulo al calcular este libro seleccione el libro que desee y luego marque o desmarque la casilla utilizar el sistema de fecha 1904 .
Problema: tener problemas con las fechas entre libros que usan diferentes sistemas de fechas
Puede encontrar problemas al copiar y pegar fechas y al crear enlaces externos entre libros basados en dos diferentes sistemas fecha Las fechas se pueden generar cuatro años antes o después de la fecha esperada. Estos problemas pueden ocurrir si utiliza Excel para Windows, Excel para Mac o ambos.
Por ejemplo, si copia la fecha 5 de julio de 2007 de un libro que usa el sistema de fechas 1900 y luego la pega en un libro que usa el sistema de fechas 1904, la fecha aparece como 5 de julio de 2011, que es 1462 días. más tarde. Además, si copia la fecha 5 de julio de 2007 de un libro que usa el sistema de fechas 1904 y luego la pega en un libro que usa el sistema de fechas 1900, la fecha será el 4 de julio de 2003, que es 1462 días antes. información general se puede encontrar en la sección Sistemas de fechas en Excel.
Corregir error de copiar y pegar
Para establecer la fecha en cuatro años y un día después, agréguele 1462.
Hoja1!$A$1+1462
Para fijar la fecha en cuatro años o antes, reste 1.462. Ejemplo
Hoja1!$A$1-1462
información adicional
Siempre puede hacer una pregunta a un especialista de Excel Tech Community, pedir ayuda en la comunidad de Respuestas y también sugerir nueva caracteristica o mejora en el sitio web de Excel User Voice
Si está trabajando con datos basados en el tiempo, es posible que necesite insertar varias fechas en la tabla. Por ejemplo, en una aplicación de seguimiento de ventas, puede ingresar una serie de fechas para un trimestre, asegurándose, por supuesto, de omitir las fechas que caen en fin de semana.
Usando Autocompletar
Mayoría método efectivo ingrese varias fechas, lo que no requiere ninguna fórmula; use la función de autocompletar. Ingrese la primera fecha y luego arrastre el controlador de relleno de celda mientras hace clic derecho. Suelte el botón del mouse y seleccione el comando del menú contextual Complete por mes(Figura 108.1).
En algunas situaciones, debe ingresar dos fechas antes de poder utilizar la función Autocompletar. Por ejemplo, si necesita ingresar fechas que sean lunes consecutivos, debe ingresar las dos primeras fechas de la serie. Luego seleccione ambas fechas, haga clic derecho y seleccione en Menú de contexto artículo Rellenar por día.
Usando fórmulas
Otra forma de introducir una serie de fechas se basa en el uso de fórmulas. La ventaja de una fórmula (en lugar de una función de autocompletar) es que puedes cambiar la primera fecha y todas las demás se actualizarán automáticamente. Ingrese la fecha de inicio en la celda y luego use fórmulas (copiando la columna) para crear fechas adicionales. Para los siguientes ejemplos, suponga que ingresó la primera fecha de la serie en la celda A1 y la fórmula en la celda A2. Luego puede copiar esta fórmula en la columna tantas veces como sea necesario.
Para generar una serie de fechas separadas por siete días, utilice esta fórmula: =A1+7.
Para crear una serie de fechas separadas por un mes, utilice la siguiente fórmula: =FECHA(AÑO(A1),MES(A1)+1,DÍA(A1))
Para crear una serie de fechas separadas por un año, use esta fórmula: =FECHA(AÑO(A1)+1,MES(A1),DÍA(A1)
Para crear una serie que incluya solo días laborables (todos los días excepto sábado y domingo), utilice la siguiente fórmula. Se supone que la fecha en la celda A1 no es un día festivo: =IF(WEEKDAY(A1)=6,A1+3,A1+1) . Esta fórmula regresa número de serie fechas, por lo que necesita formatear las celdas para mostrar las fechas.
El más simple y de manera rápida Para ingresar la fecha u hora actual en una celda es presionar la combinación de teclas de acceso rápido CTRL+“;” (fecha actual) y CTRL+MAYÚS+“;” (tiempo actual).
Es mucho más eficiente utilizar la función HOY(). Después de todo, no sólo establece, sino que también actualiza automáticamente el valor de la celda todos los días sin intervención del usuario.
Cómo poner la fecha actual en Excel
Para insertar la fecha actual en Excel, use la función HOY(). Para hacer esto, seleccione la herramienta “Fórmulas” - “Fecha y Hora” - “HOY”. Esta función no tiene argumentos, por lo que simplemente puede escribir “=HOY()” en la celda y presionar ENTER.
Fecha actual en la celda:
Si es necesario que el valor en la celda se actualice automáticamente no solo fecha actual, pero entonces es mejor usar la función “=TDATE()”.
La fecha y hora actuales en la celda.
Cómo configurar la fecha actual en Excel en encabezados y pies de página
Insertar la fecha actual en Excel se implementa de varias maneras:
- Configurando opciones de encabezado y pie de página. La ventaja de este método es que la fecha y hora actuales se imprimen en todas las páginas al mismo tiempo.
- Usando la función HOY().
- Usando la combinación de teclas de acceso rápido CTRL+; – para establecer la fecha actual y CTRL+SHIFT+; – para configurar la hora actual. La desventaja es este método El valor de la celda no se actualizará automáticamente a los valores actuales al abrir el documento. Pero en algunos casos, la falta de datos es una ventaja.
- Usando macros VBA usando funciones en el código del programa: Fecha();Hora();Ahora().
Los encabezados y pies de página le permiten establecer la fecha y hora actuales en la parte superior o inferior de las páginas del documento que se enviará a la impresora. Además, el pie de página nos permite numerar todas las páginas del documento.
Para hacer la fecha actual en Excel y la numeración de páginas usando encabezados y pies de página, haga esto:
Los encabezados y pies de página nos permiten hacer más que simplemente establecer fechas y números de página. También se puede agregar un lugar para la firma del responsable del informe. Por ejemplo, ahora editemos la parte inferior izquierda de la página en el área del encabezado y pie de página.