Fórmulas y funciones de Excel Parte 7: Funciones de fecha y hora

1. Introducción a las funciones de la fecha y el tiempo: Está bien. Bienvenidos de nuevo, todos. Se trata de la Sección siete. Funciones de fecha y hora. En una de mis secciones favoritas se baja una de las secciones más importantes de todo este curso de capacitación, simplemente por que muchas personas se encuentran con una serie de temas y dolores de cabeza tratando trabajar con fechas y horarios en excel. Sé que pasé alrededor de un año especie de hurto a través de estos no entendiendo del todo lo que estaba pasando. Um, hasta que realmente me senté a entender cómo Excel está pensando en las fechas y cómo sobresale procesamiento de fechas. Y una vez que obtuve ese entendimiento, en realidad es bastante simple, y lo hizo todo más fácil a la hora de trabajar con fechas y horarios. Entonces vamos a empezar con una fecha Sección Time 101 o intro. Hablaremos del valor de fecha, que es una especie de pieza clave aquí sobre por qué va a ser tu nuevo mejor amigo a la hora de trabajar con fechas y horas y excel talk date formato y luego la opción de llenar Siri, que es un bonito atajo para crear fechas secuenciales a las que vamos hoy y ahora , que son funciones volátiles que devuelven la fecha actual y la hora exacta actual hablaremos año, mes, día, hora, minuto y segundo, que acuerdan formas de tipo de categorizar sus datos de fecha. Hablaremos de fin de mes y tu frack yendo a días laborables de red día laborable. Esos tips de aire fresco para tipo de mostrar cuántos días laborales caen entre dos fechas? qué fecha sigue un cierto número de días laborales a partir de hoy, así sucesivamente y demás? Y luego terminaremos con date diff, que es otra herramienta para calcular diferencias entre dos fechas y luego terminaremos con la , uh, una muy útil demo de manos sobre, donde te voy a mostrar cómo construye tu propia herramienta de ritmo de presupuesto, que puedes personalizar y transformar en cualquier cosa que quieras. Dos archivos para descargar consiguieron nuestro Excel para analistas funciones de fecha y hora. PDF y su sección siete fecha Funciones de tiempo Excel File, cual estará caminando a medida que nos movamos por las conferencias. Así que adelante y descarga esos dos archivos. Podrías imprimirlos si eliges y, uh, uh, vamos a bucear justo en 2. DATEVALUE: tu nuevo BFF: Está bien. Entonces, ante todo, quiero hablar de algo llamado el valor de la fecha. Y si hay una conferencia que va a escuchar o una diapositiva que vas a leer en toda esta sección, ésta es a la que prestar atención. Y es porque el valor de fecha es la pieza crítica que permite a excel procesar fechas y horas así cada fecha. De hecho, cada momento en el tiempo en Excel tiene un ID asociado subyacente valor de fecha numérica. Es así como sobresale, capaz de hacer cosas como calcular el paso del tiempo o aplicar funciones estadísticas a las fechas sin esos valores de fecha las fechas tipo de simplemente se tratarían como campos de texto. Realmente no serías capaz de hacer mucho. Ah, en términos de manipulación con esas fechas. Por lo que la medianoche del 1 de enero de 1900 se trata como, supongo, el inicio de los tiempos en el universo sobresale. Por lo que el 1 de enero de 1900 tiene un valor de fecha subyacente de uno y luego cada día subsiguiente después del 1 de enero de 1900 tiene un valor de fecha un número mayor, por lo que el 2 de enero de 1900 tiene un valor de fecha de dos y así sucesivamente y así sucesivamente. Por lo que Excel es realmente, realmente inteligente sobre las fechas en, y luego reconoce la mayoría de las fechas de tipo y aplica automáticamente un formato de fecha común. En la mayoría de los casos, será mes slash day slash year, con cuatro dígitos junto con su valor de fecha asociado. Ahora la forma de comprobar si tu entrada es reconocida como fecha es hacer click derecho yendo a vender , formatear y cambiar a general, y te mostrará si hay un valor de fecha subyacente. Entonces te mostraré un ejemplo de eso ahora mismo. Si salto a un cuaderno en blanco aquí, voy a escribir una fecha a mano. Voy a hacer 1 enero 2015 percenter y tener un click derecho a uno va a formatear celdas . Um, como puedes ver, por defecto el campo de fecha. Esta es la versión predeterminada Dave Month year. Pero si cambio a la categoría general, se puede ver que tiene un valor subyacente de 42,000 y cinco porque es el día que ha caído 42,005 días después del 1 de enero de 1900 s. Entonces eso me dice que está bien, Excel tiene reconoció mi entrada como una fecha que puede tratarla como tal. También es bastante bueno sobre entender, tipo de uno extrañamente formateado. Entonces podría decir como, Oh un dash Owen, Dash 15 y se puede ver que lo cambió a la versión predeterminada porque sabía que estaba tratando de escribir una fecha. En realidad puedes ponerte como, un poco raro con él. Ya sabes, me gusta especie de prueba para ver lo extraño que puedo conseguir. Ver hasta esa versión donde la escribo. Como saben, guión y combinación de guiones y barra inclinada, todavía lo reconoció como una fecha, y aún así le aplicó ese valor de fecha. Por lo que Excel es en realidad bastante inteligente cuando se trata de esas cosas encendidas. Y esa es una especie de la razón central por la que son capaces de hacer tan interesantes manipulaciones y cálculos contra estados eso si le sucede a tipo de fecha. Eso, excepto que no reconoce un ejemplo común que he visto es si escribes una serie de números como fecha mes día. Entonces para el 1 de enero 2015 de nuevo si escribiste 2015 0101 sin guiones ni barra. Excel no reconocería eso como una fecha, y por lo tanto no se aplicaría en bajo valor de fecha subyacente, y lo trataría como una cadena de texto o, en este caso, valor. Entonces si ese es el caso como si descargo algunos datos y el campo de fecha está en un formato muy extraño como ese, te mostraré cómo usar herramientas como valuador de fecha, valor de tiempo para convertir esas fechas sin formato en dos formatos que Excel entiende. Entonces viendo algunos ejemplos aquí abajo otra vez, 1 de enero de 1900 es la fecha uno, por lo que el valor de fecha es igual a uno 10 días después. El 11 de 1900 tendría un valor de fecha de 11. El 6 de febrero es de 42,041 días después del 1 de enero de 1900 y estos valores de fecha no tienen por qué ser números enteros. De hecho, 42 0 41.5 equivale al mediodía del 6 de febrero, mientras que 42 0 41.75 equivale a las 6 de la noche que es el 75% del camino a través del día. Por lo que se puede conseguir muy granular incluso hasta el segundo en. Es así como puedes empezar a trabajar con tiempos muy particulares en Excel, no solo mantener fechas, así que ojalá eso ayude Una vez que entiendas este concepto de valor de fecha, repente todo se vuelve un poco más claro. Empezamos a hablar de realmente manipular y aplicar funciones a estas fechas. 3. Formatoy completa de la fecha: por lo que solo quiero cubrir dos consejos y conceptos más antes de sumergirnos en las propias funciones . El primero es el formato de fecha, por lo que cuando formateas fechas y excel, tienes algunas opciones. Puede seleccionar una opción preajustada de la categoría de fecha en el cuadro de diálogo Formato Celdas , que puede acceder con solo hacer clic derecho en una celda eligiendo formato sur. O tienes la opción de crear tu propio formato personalizado. Por lo que los formatos preestablecidos vuelven a vivir en la línea de fecha o en la categoría de fecha del cuadro de diálogo de celdas de formato . Y hay una serie de opciones. Pedir disculpas. Es un poco complicado de leer, pero podrías desplazarte hacia abajo y podrías mostrar tu fecha con una serie de variaciones diferentes . Podrías mostrar sólo el día y el mes, no el año. Se puede mostrar como un año de dos dígitos frente a cuatro. Podrías escribir el día y dejar el año como número, como si hubiera decenas y decenas de formas de demostrarlo y probablemente tenga lo que necesitas. La otra opción es saltar hacia abajo a la categoría personalizada y escribir tus propios formatos aquí, Así que aquí los códigos que puedes usar. Esto es sólo una muestra de ellos. El listado completo está disponible en soporte dot office dot com, pero esto solo te permite mostrar una fecha de otras formas adicionales. Entonces, por ejemplo, si tengo una fecha en una celda como el 1 de enero de 2015 ah, y en lugar de mostrar toda la fecha, solo quiero mostrar qué día de la semana es ese. Podría formatear esa celda con un formato personalizado de de de, y se mostrará el valor como sábado o viernes o miércoles. No sé si la parte superior mi cabeza qué día de la semana fue ese. Pero, um, importante tener en cuenta que no va a cambiar el valor subyacente en sí. Sólo cambiará la forma en que lo estás mostrando. Segunda cosa que quiero mostrarte es la de Phil Siri, que es un gran atajo que utiliza Excel para básicamente ayudarte a poblar una serie de fechas sin tener dedo del pie tipo manual en días o meses o años secuenciales. Entonces digamos que escribí 11 2015 y vendo uno aquí. Excel lo ha reconocido como una fecha. Si agarro la esquina inferior derecha de esa celda y la arrastro hacia abajo a un cinco por defecto. Excel va a asumir que quiero mostrarlo como tendencia de estancia a lo largo del día. Entonces básicamente agregando un día por cada fila. Si digo que no, no quiero hacer eso. Yo sólo quiero una copia. 11 2015 y cada celda puedo dar click en este pequeño botón que aparece después de que hayas arrastrado los valores y puedo comprobar las ventas de copia. Ya verás que está impedido llenar serio, lo que supone que quiero día tras día, día tras día y después tengo algunas otras opciones. Aquí está Bueno, puedo llenar sólo el formateo de Sally uno. Podría llenarlo sin el formateo o si quisiera mostrar la secuencia a un nivel distinto al día. Yo también tengo esas opciones, así que puedo decir Ok, lugar de ir en incrementos de días, vamos a mostrarlo en incrementos de meses, en cuyo caso esto sería 1121314151 o años, en cuyo caso se sería 11 2015 2016 17 18 19 o incluso días de semana por lo que puedo decir aumentar la fecha en un día. Purcell, pero excluyen los fines de semana. Por lo que esta es una manera realmente genial de crear ventanas de fecha rodante o automatizar mucho del proceso cuando estás trabajando con conjuntos de datos centrados en fechas que están cambiando constantemente. Así que ten en cuenta estos dos trucos, los vamos a estar usando bastante en algunos de los próximos ejercicios. 4. TODAY()/NOW(): bien, las primeras funciones a la fecha y hora que quiero compartir con ustedes, nuestras hoy y ahora las cuales están diseñadas para simplemente devolver la fecha actual o la hora exacta actual . Y la buena noticia es que estos airen quizá dos de las funciones más simples en excel y son tan simples porque en realidad ni siquiera se refieren a otras células. Acabas de abrir y cerrar tus paréntesis. Ni siquiera pongas nada dentro. Llámalo un día y ya estás bien para irte. Um, importante tener en cuenta que se trata de funciones volátiles, lo que significa que se actualizarán en cualquier momento que realices algún cambio de hoja de trabajo en cualquier libro abierto. Por lo que habrá cambios constantes. Saltemos a excel y, uh, uh, adelante y abrimos la sección siete Funciones de fecha y hora. Voy a hacer un paseo rápido por, como se puede ver en la primera pestaña funciones diurnas. Tengo un marcador de posición para la fecha actual y la hora actual, que vamos a poblar en tan solo un minuto usando hoy y ahora también tenemos una hipotética fecha de vencimiento del proyecto, a la que llegaremos más adelante. Y entonces aquí es básicamente donde vamos a practicar todas las funciones que aprendimos en las próximas conferencias. El segundo tabulador es una herramienta de ritmo presupuestario en blanco. Esta es la demo que vamos a recorrer al final de la sección donde vamos a trabajar juntos y usar las funciones de fecha y hora para construir una herramienta personalizada de ritmo presupuestario . Y luego por último pero no menos importante en la tercera pestaña llamada datos de muestra. Simplemente tenemos un Siris de fechas y gastos y estos aire van a alimentar a la herramienta de ritmo presupuestario que construimos. Por lo que saltar de nuevo a la fecha funciones de hora en Tom one. Sólo poblemos estos hoy y ahora funciones en la celda C dos y C tres. Entonces para la fecha actual, solo voy a escribir igual hoy. Abrir, cerrar. Entrar ESO devuelve la fecha actual, que es el 18 de agosto de 2015 Hora actual. Mismo proceso igual Ahora abrir cerrar presionar enter. Y como puedes ver, están devolviendo lo mismo excepto la hora actual o la función ahorita es solo un poco más granular que introducirá los componentes de hora, minuto y segundo que la fecha actual o la función de hoy no lo hará. Entonces ahí vas extremadamente simple, pero este va a ser nuestro punto de partida para practicar las funciones en las próximas conferencias 5. AÑO/MES/DÍA y HORARIO/MINUTO/SEGUNO/SEGUNDO: por lo que Excel siempre calculará fechas y horas en función de sus valores subyacentes precisos de cereal o valores de fecha. Pero, ¿y si necesitas trabajar con valores menos específicos? Entonces, ¿y si no te importa el día específico? Simplemente preocúpate por el mes o el año, o incluso la hora o el minuto. Um, si necesitas acumular tus datos de una manera menos granular y ver las cosas a un nivel superior, hay número de fórmulas de serialización que te permiten hacer eso, por lo que las más comunes incluyen año, mes y día y hora, minuto y segundo. Básicamente, todo lo que hacen es extraer esos componentes individuales fuera de una fecha determinada. Tan sabia sintaxis, son casi tan fáciles como se pone eso escrito exactamente como cabría esperar. Por lo que el año es año, mes , este mes , Días, día y demás, y el único componente real de la fórmula es la referencia a una celda que contiene una fecha u hora. Así que saltemos a Excel y escribamos algunos de estos. Lo que voy a hacer es poblar las celdas C cinco a C 10 para extraer año, mes, día, hora, minuto y segundo de nuestros campos actuales de fecha y hora. Por lo que el año en curso sólo va a ser igual al año y el número de serie o referencia que podría ser ya sea C dos o C tres en este caso. Por lo que empiezo con C para cerrarlo. Ya verás que extrae 2015 porque esa es la fecha del día actual. También podría cambiar la referencia. Te voy a mostrar un atajo aquí en lugar de entrar y cambiar esto y escribir c tres. Um, obviamente, eso no sería muy difícil. Pero lo que voy a hacer en su lugar es presionar F dos para sumergirse y luego simplemente agarrar mi referencia y arrastrarla hacia abajo para ver tres presionar entrar. Eso logrará exactamente lo mismo pasando a mes. Misma historia aquí, hacer mes de C tres días de C tres. Entonces eso me está diciendo que estamos en el octavo mes y el día 18 del mes Ahora, para nuestro minuto y segundo, tengo que referirme a la función ahorita porque la función de hoy no me da ese nivel de detalle. Entonces si traté de hacer nuestro de C dos, sólo me va a dar cero porque no hay nuestro especificado, um saliendo de esa fórmula. Entonces lo que volveremos a hacer es simplemente cambiar la referencia. Arrástrelo hacia abajo y ahora se está vendiendo señora en la hora 12 del día por minuto otra vez, refiérase a ver 3/30 minuto y finalmente, segundo de Seat tres es 16 y otra vez. Debido a que se trata de funciones volátiles, puedes verlos cambiar en cualquier momento que realices cualquier actualización de cuaderno de trabajo. Entonces si escribo algunos valores aquí, ver ese segundo valor cambiando o si fui a la pestaña de fórmulas, comprimió este botón calcular ahora y vi los segundos marcan. Entonces ahí lo tienes. Es una forma muy sencilla pero muy valiosa de extraer componentes específicos de una fecha. 6. La función EOMONTH: a continuación, tenemos el fin de mes o e o mes. Función, que calcula el último día de un mes determinado, también se puede utilizar para calcular, fechas de inicio o finalización de meses anteriores o futuros también. Entonces la sintaxis sabia, solo dos componentes de esta fórmula. Comienza con tu fecha de inicio. Esa podría ser una fecha actual. Podría ser una fecha aleatoria. Podría ser cualquier referencia celular a una celda que contenga una fecha y luego el componente meses, que es el número de meses antes o después de su fecha actual de inicio. Entonces si me interesa el último día del mes en curso, pondría un cero por meses aquí. Si me interesa la última fecha del próximo mes, pondría una positiva. Si buscaba la última fecha del mes anterior, pondría una negativa y así sucesivamente. Entonces digamos que tenemos una fecha de inicio o fecha actual de 83 2015 en auto ver dos. Aquí, podemos usar la función Mes EOE de varias maneras. Si queremos calcular el final del mes en curso, reducimos Eo Mes C dos comas cero. Um, para calcular este inicio del mes, tienes que ponerte un poco inteligente, pero puedes usar la función Mes EOE para hacer eso. Entonces, por instancia, el inicio del mes en curso podría calcularse usando el Mes Eo ver a uno negativo, lo que me da la última fecha del mes anterior y luego solo agregar una para obtener la primera fecha del actual mes. Entonces recuerda que las fechas realmente son solo valores por ese valor de fecha subyacente. Por lo que agregando uno a ella esencialmente sólo impuesto en un día. Entonces caso similar aquí para obtener esta fecha de inicio del próximo mes debilita, digamos, eo mes, Ver a cero común para obtener el último día de este mes y luego agregar uno para obtener la fecha de inicio del siguiente mes. Entonces es saltar para sobresalir y en realidad practicar esto, um, para el último día del mes y vender ver 12. Todo lo que necesito hacer es justo igual a e o mes. Y entonces mi inicio, fecha o referencia de venta a una fecha podría ser C dos o C tres. Simplemente voy a elegir C a aquí y lo que voy a hacer es presionar F cuatro para arreglar esa referencia, que le dará la opción de copiar y pegar esta fórmula en otro lugar mientras se sigue refiriendo a asiento a. Entonces no quiero que esa referencia me pase coma a la siguiente pieza, que son meses. Y en este caso, me gustaría el último día del mes en curso. Entonces solo voy a poner un cero ahí, cerrar el paréntesis y presionar enter. Entonces el resultado es del 4 al 247 Y si prestaste atención a las primeras conferencias, probablemente reconocerás que este es un valor de fecha que me está mostrando. Eso significa que esta celda está formateada con el tipo de formato general. Por lo que podría hacer es hacer clic derecho ir a formatear celdas, cambiarlo a la fecha, y luego seleccionar cualquier opción que elija aquí. Entonces voy a ir con la opción predeterminada, uh, mes, uh, mes, día año para nosotros. Está bien. Y ahí tienes. 8 31 2015 Ten en cuenta que dependiendo de cuando realmente estés haciendo estos ejercicios, aquí vas a ver diferentes valores y fechas que yo. No te preocupes por eso. los conceptos y fundamentos deben seguir aplicándose. En cualquier caso, estoy así para conseguir el primer día del mes, realidad voy a copiar esta fórmula, pegarla hacia abajo, y ahora recuerda el pequeño truco en lugar de cero, que aplica al mes actual. Quiero la última fecha del mes anterior. Entonces voy a poner uno negativo, y luego saltamos fuera del paréntesis y le agregamos un día más, lo que me llevará al primer día del mes en curso, que es el 81 2015. Entonces ahora, último pero no menos importante, primer día del año, este es un poco más engañoso. Ahí un par de formas de hacerlo. El primer modo es simplemente copiar y pegar esa misma fórmula que acabo de escribir. Pero en lugar de mirar un mes en el pasado, voy a mirar ocho meses en el pasado porque el mes actual es agosto u ocho. Entonces esto me conseguirá el último día de diciembre del año pasado, y luego voy a sumar uno, que debería regresar el primer día de su año en curso. Mi prensa entrar. Eso parecía funcionar. Entonces conseguí 11 2015. Ahora cuando lo estás haciendo en casa, posible que no restes ocho meses, dependiendo del mes que estés mirando. Por lo que podemos modificar esta fórmula para que funcione sin importar cuál sea tu fecha actual y hora actual . Y para hacer eso, sólo voy a reemplazar el menos ocho aquí. Entonces lo que necesito hacer es restar el número del mes actual, que me requiere saber cuál es el número del mes y luego multiplicarlo por uno negativo para obtener el componente del mes correcto para esta fórmula. Entonces en enfermedad de impresión, voy a hacer negativo una vez meses de fecha actual y luego cerrar todos esos paréntesis. Entonces para recapitular lo que acabo de hacer ahora lo que estoy diciendo es, quiero el fin de mes. Somos Mike fecha actual o punto de partida de c dos. Pero el número de meses que quiero mirar atrás en el tiempo es una función de lo que es mi mes actual . Entonces esto está diciendo que si actualmente estoy en el mes ocho del año, quiero volver atrás en el tiempo ocho meses y conseguirme el último día de hace ocho meses. Si estoy en el décimo mes del año. Yo quiero saltar atrás y conseguir los últimos ocho de hace 10 meses y así sucesivamente y así sucesivamente. Y entonces otra vez, esta última pieza es solo atacar a ese día extra para conseguir el primero del mes siguiente . Entonces si presionamos enter, ahí tienes. Obtenemos la misma respuesta 11 2015 y que ahora regresará el primer día del año, independientemente de cuál sea tu fecha y hora actual. Entonces como puedes ver, E O mes es una función realmente útil. Puedes usarlo para hacer una serie de cosas muy creativas, y es una herramienta bastante crítica para los usuarios de Excel que trabajan con fechas y horarios porque realmente no hay una manera más sencilla de hacerlo. Dado que el número de días al mes varía, este tipo de automatiza mucho de ese tedioso trabajo manual que tendrías que hacer de otra manera. Por lo que ahí vas E O función mes 7. La función de YEARFRAC: la siguiente función de la que quiero hablar. Se llama year frack, que calcula la fracción del año representada por la diferencia entre dos fechas. lo tanto, típicamente, esto se utiliza para comparar la primera fecha de un año determinado frente a la fecha actual para mostrar qué porcentaje del año estás a través. Pero puedes hacerlo por dos fechas cualquiera y mirarlo. Básicamente, alguien sabe que eliges tan sintaxis sabia, tres componentes a esta fórmula que obtuviste tu fecha de inicio, tu fecha de finalización, y tu base base base solo te permite especificar el tipo de conteo de días a usar o, en otras palabras, cómo estás definiendo el número de días en un año. Um, cero es el valor predeterminado. En realidad recomendaría poner uno aquí, que mira el número real de días en el año real en el que estás basado en tus fechas de inicio y fin. Entonces eso dará cuenta de cosas como, uh, años bisiestos, que podrían tener un día adicional 366 contra 365 para el año anterior o el año siguiente. Entonces aquí hay un ejemplo. Si tienes una fecha de inicio en Selby 211 2015 y una fecha de fin en la celda B tres 2 28 2015 Aquí por ejemplo, salidas en el primer caso estamos usando la única base, que es real. Y eso nos está diciendo que el 28 de febrero de 2015 fueron 15.9% del camino a través del año en adelante . Y eso es a través del año real. 2015 si cambiamos la base a ese porcentaje cambia ligeramente a 16.1, porque ahora se basa en una base de 360 por año. Así que pro tip aquí year frack es una herramienta realmente genial para cosas como ritmo y cálculos de proyección . Echemos un vistazo a Excel y básicamente vamos a usar los datos que ya ingresamos aquí para calcular el porcentaje a lo largo del año de nuestra fecha actual. Entonces voy a hacer igual que tú eres frack, abre el paréntesis, nuestra fecha de inicio va a usar el primer día del año, que calculamos en la última conferencia y luego la fecha de fin y ver, y entonces mi base va ser uno. Cuando cierro eso, obtengo 63%. Entonces eso me está diciendo que hoy el 18 de agosto. Soy 63% del camino a través del año. Entonces ahí tienes. Eres frack. 8. WEEKDAY, días de trabajo y los días de red: bien, así que hablemos de tres funciones más de fecha o en plática día laborable, jornada laboral y días de red. Por lo que a partir del día de la semana uno realmente valioso. Te muestra en qué día de la semana cae una fecha determinada, y hay dos formas en que puedes hacerlo. El primero es solo usar un formato de celda personalizado como hablamos en conferencia a, Así que puedes usar ya sea tres DS para obtener el día abreviado de la semana o cuatro D's para obtener el día completo de la semana y otra vez note que esto no está cambiando realmente el valor o creando cualquier de nuevo valor. Está en Lee cambiando cómo se te está mostrando el valor de fecha original. El segundo, que es el enfoque que recomendaría, es usar la función de día de la semana, que en realidad devuelve un valor de serie correspondiente a un Dave en particular esa semana, ya sea de uno a siete o de cero a seis. Entonces esta sintaxis es bastante simple. Tienes primero tu número de serie, que es vender que contiene una fecha o una hora y luego tu tipo de devolución, que es opcional donde cero es tu predeterminado, que asigna un número uno a un domingo, hasta el número siete, que es sábado. Um, si prefieres llamar al lunes el inicio de la semana y asignarle uno al lunes, podrías poner uno aquí en lugar de un cero o en lugar de dejarlo en blanco eso una función realmente útil, sobre todo cuando se trata de haciendo cosas como análisis del Día de la Semana donde queremos ver cómo difiere el rendimiento o los datos, UH, martes versus un viernes frente a un miércoles y así sucesivamente, Um, día de trabajo y días de red. Entonces día de trabajo, para ser totalmente honesto, es un poco confuso. Básicamente devuelve una fecha que es un número especificado de días antes o después de una fecha de inicio determinada , excluyendo fines de semana y días festivos, si opta por agregar esos. Entonces la aplicación más común que se me ocurre es decir en qué fecha cae 50 días hábiles a partir de hoy. Entonces una vez que llego a través de 50 días hábiles, que no incluye fines de semana, en qué fecha voy a aterrizar Y así pecar sabia de impuestos, empieza con tu fecha de inicio, luego va al número de días. Aquí puedes poner un valor positivo si quieres mirar hacia adelante en el futuro o valor negativo. Si quieres mirar atrás en el pasado. Esos airean todos los componentes requeridos. Existe un componente de vacaciones opcional que te permite hacer referencia a una lista de días festivos, y esencialmente excluirá aquellos del número de días hábiles que está calculando . Entonces ese es el Día del Trabajo. Network Day, que en realidad encuentran mucho más útil y más práctico, te muestra el número de días laborales entre dos fechas. Por lo que tú y pongas una fecha de inicio en fecha de fin y una referencia opcional a una lista de vacaciones, y te dirá cuántos días hábiles o días hábiles cayeron entre esas fechas. Entonces dos ejemplos aquí tengo las mismas referencias a la fecha que mencioné en la última conferencia, que son el conferencia, primero de enero de 2015 como nuestro inicio, 28 de febrero de 2015 como nuestro fin. Entonces si decimos jornada laboral, sé demasiado común 20 ese dicho desde la fecha de inicio del 1 de enero de 2015. ¿ Qué fecha cae 20 días hábiles después de eso, y regresará la fecha 29 de enero de 2015 si usamos la función de días de red y establecer B dos es nuestra fecha de inicio, y B tres está ahí terminado. Nos dirá que entre el 1 de enero y el 28 de febrero de 2015. Fueron 42 días hábiles, que es el total de días menos todos los fines de semana. Entonces vamos a saltar a excel y ver tipo de cómo funciona esto en la práctica. Entonces en F tres, tengo una celda de fecha de vencimiento del proyecto, y sólo voy a inventar alguna fecha. En el futuro, eso no significa nada. Entonces digamos el 9 de mayo de 2017. Si este es un gran proyecto, va a llevar mucho tiempo. Días hasta hacer esto es realmente simple. Desde valores de fecha aire realmente solo valores que puedo realizar cualquier operación normal en igual que lo haría cualquier otro número, solo voy a decir la fecha de vencimiento menos la fecha actual ahora devuelve 6 30 Así que ten 630 días entre el 18 de agosto de 2015 y 9 de mayo de 2017. Entonces ese es el total de días hasta hacer ahora. Si quiero el número de días laborales hasta hacer obviamente no voy a pasar mis fines de semana trabajando en este proyecto. Puedo usar días de red, por lo que la fecha de inicio de los días laborables netos es la fecha actual. La fecha de finalización 18 es la fecha de vencimiento. Entonces si dejo en blanco el campo de vacaciones opcional. Cierra esos paréntesis. Impresiona centro. Eso me está diciendo que bueno, tengo 630 días totales hasta que se haga solo tengo 451 días hábiles hasta que lo haga por la pieza de vacaciones. Lo que no puedo hacer es simplemente escribir un número de vacaciones como cinco y decir OK, excluir esos cinco días festivos. Simplemente devolverás el mismo valor de 4 51 Lo que necesita aquí es una referencia real a una lista de fechas que definen las vacaciones que quiero excluir eso lo que puedo hacer sólo por el bien de un ejemplo. Digamos 12 25 2015. Quiero despegar la Navidad y quiero despegar el 1 de enero de 2016. Y esas son las dos fiestas entre la fecha de hoy y el 9 de mayo de 2017 que planeo despegar y no trabajar. Entonces qué, puedo hacerlo decir OK, en lugar de poner un cinco ahí, me voy a referir a la edad 536 que es mi lista de fechas de vacaciones y presionar enter, y ahora puedes verlo actualizado a 4 49 por lo que excluyó estas fechas de vacaciones también. Tenga en cuenta que si tiene unas vacaciones que también cae en un fin de semana, ya se habría excluido aquí. Pero en este caso, ambas fechas son de lunes a viernes, por lo que se estaban contando en el total de 4 51. Y así cuando agrego esa referencia en , excluye esos dos y se va. Trasladar 449 días hábiles hasta que el proyecto sea vencido. último pero no menos importante para el día de la semana de nuevo, podría simplemente hacer clic derecho e ir a formatear celdas en el campo de fecha real y decir, Vamos a hacer un custom delete this type line y poner Dee dee dee dee dee dee. De acuerdo, es martes, pero quiero seguir mostrando la fecha en su formato actual. Y sólo tienes que añadir el día de la semana por separado aquí. Entonces voy a usar la función día semana igual a semana, día del número de serie, que es F tres. Voy a dejar el tipo de devolución por defecto, así que me dan un tres. Y cuando formatee los tres que pueden cambiar eso a custom de de de y ahí vas me da martes. Por lo que el 9 de mayo de 2017 es un martes. Entonces ahí lo tienes. Hay día laborable entre semana y días de red 9. La función DATEDIF: Por lo que la última función de la que quiero hablar en este apartado se llama date def. Y calcula el número de días, meses o años entre dos fechas dadas. Entonces tres componentes de la sintaxis de la fórmula comienza con la fecha de inicio, luego tú y pon una fecha de finalización. El tercer trozo es la unidad. Entonces esto es determinar cómo se quiere calcular la diferencia entre los dos días. Se puede establecer un d aquí para calcular, ya que el número de días entre las fechas M devolvería el número de meses entre fechas. ¿ Por qué devolvería el número de años entre fechas? También hay opciones como M d y d o por qué m. que calculará estas diferencias independientemente de los meses o independientemente de los años. Entonces dos ejemplos aquí otra vez, estamos viendo las mismas fechas de inicio y fin que usamos en los dos ejemplos anteriores. Enero 1 2015 Zehr inicio 28 de febrero 2015 es nuestro fin. Entonces si establecemos el diff de fecha entre su fecha de inicio y estar a en nuestra fecha de fin y B tres y dijo la unidad dos días, regresará 58. Ya que son 58 días entre estos estados y tenga en cuenta que esto funciona como una función de días de red excepto los días de red excluyendo los fines de semana. Esto está devolviendo todas las fechas y también nota que, uh, esto está haciendo exactamente lo mismo que solo escribir la fórmula B tres menos B dos. Por lo tanto pro tip. Si solo necesitas calcular el número de días entre fechas, solo usa la resta simple. No es necesario usar una fecha si la función para eso. Pero donde la fecha sí funcionaba se vuelve mucho más valiosa y donde no se puede replicar . Usar operaciones simples es si quieres calcular una diferencia en términos de meses o años, o alguien y así sucesivamente. Entonces si queremos decir fecha diff entre el 1 de enero y el 28 de febrero con la unidad M D, que es el número de días entre fechas ignorando meses y años, va a decir que son 27 días entre el 28 y el primero. todas formas, si estoy viendo un mes diferente o un año diferente, echemos un vistazo a Excel y practiquemos esto. Por lo que para volver a visitar este proyecto fecha de vencimiento que habíamos escrito en la última conferencia. Lo que puedo hacer aquí es, si quiero calcular los meses que quedan hasta que vence mi proyecto, puedo escribir igual fecha sorda. Y entonces no sé por qué este es el caso, pero Excel no te ayuda a través de esta función. Simplemente te deja colgado. No estoy seguro si es un error o qué, pero si confiamos en nuestra memoria, sabemos que el primer componente de la fecha, def, es la fecha de inicio, que es mi fecha actual. Y entonces la fecha de fin va a ser la fecha de vencimiento. Y entonces la última pieza es la unidad, ¿recuerdas? Entonces en este caso, necesito los meses que quedan, Así que voy a poner una M entre comillas. Y esto no funcionará a menos que ponga aquí la M entre comillas. Entonces sólo ten eso en cuenta. Y luego cuando golpee enter, eso me dice que me quedan 20 meses desde la fecha actual hasta la fecha en que mi proyecto es debido y luego caso similar aquí, lo que voy a hacer en lugar de escribir esto desde cero, estoy solo voy a arreglar las referencias en mis funciones originales de fijo el C dos y arreglar el F tres. Copiar esa fórmula, pegarla hacia abajo y cambiar la M a un Why, ya que quiero los años restantes. Entonces me está diciendo que me queda un año hasta que llegue mi proyecto. También podría saltar de nuevo para vender F cinco, donde había calculado mis días hasta hacer como solo f tres menos C dos y veamos si podemos obtener la misma respuesta. 630 usando una fecha sí funcionaba. Entonces voy a hacer cita sorda C dos coma F tres comas de entre comillas y ahí vas 6 30 así que otra vez dos formas de hacer lo mismo y no sé de ti, pero preferiría solo decir F tres menos C dos y llamarlo un día. Pero la fecha si puede ser una gran opción, sobre todo si se quiere agregar la diferencia entre dos fechas a nivelde mes o año nivel 10. SHOWCASE: de la ESPANA: diseña una herramienta de SHOWCASE: pa, de la muestra de un presupuesto de l: Está bien. Bienvenidos al final de la sección siete. Felicidades por lograrlo a través. Hablamos de una tonelada de funciones diferentes, útiles de fecha y hora, y ahora es el momento de poner en práctica algunas de ellas. Entonces lo que vamos a hacer es una especie de paso a paso caminar por este proyecto que incorpora un montón de las diferentes funciones del tiempo de citas en tándem con algunas de las funciones que aprendimos antes en nuestro entrenamiento. Entonces hagámoslo. Um, lo primero que necesitamos es la fecha actual y ver y contrario a lo que podrías estar pensando, en realidad no quiero usar aquí la función de hoy. Um, es un poco engañoso, pero no quiero volver qué día es hoy. Deseo devolver el último día en mi conjunto de datos. Entonces si recuerdas este dato de muestra tiene gasto declarado por día bajando del dedo del pie 16 de mayo. Entonces si no tengo datos de gastos después de eso y escribo hoy, que es en agosto, esta herramienta no va a ser muy útil. Entonces en su lugar voy a usar una función max. Yo solo quiero devolver la fecha máxima de la columna A de mis datos de muestra, Ted, Ahora regresaremos 5 16 El otro beneficio de hacer esto es que cuando estás trabajando por tu cuenta este ejercicio en casa y usas la función de hoy, no va a jive con datos de muestra y realmente no tendrá mucho sentido. Entonces, por ahora, solo usa la función del Mac. Y si terminas construyendo uno de estos o personalizando uno de estos para tu propio uso, puedes cambiarlo como más te convenga. Yo soy así inicio Fecha va a ser el primer día del mes en curso otra vez el significado actual últimos datos en nuestro conjunto de datos. Entonces voy a usar la función Mes EOE aquí. Fecha de inicio. Va a ser C dos y recuerda que para conseguir la primera fecha del mes en curso, necesito tomar la última fecha del mes anterior así que menos uno más un día, eso me da 51 2015 que es exactamente lo que quiero ahora por ciento de mes. Este parece que debería ser realmente fácil, pero en realidad es un poco más engañoso de lo que pensarías porque realmente no hay función frack de mes . Uh, ya sabes, año. Frack es realmente útil porque solo podrías decir Dime el porcentaje a través del año que estoy No hay un equivalente para frack de mes y los retos que cada mes tiene un número diferente de días. Por lo que sé que estoy 15 días del camino a través de mayo. Pero, ¿divido eso por 30 días o 31 o 29? Entonces lo que necesitamos es una mesa pequeña útil, que convenientemente he agregado aquí en las celdas B 24 a C 35 que me dice el número de días en cada mes en base al mes número s para que pueda usar eso para calcular mi porcentaje del mes es igual a mi fecha actual menos mi fecha de inicio. Y rodean esa paz entre paréntesis. Ese es el número de días a través del mes que actualmente estoy. Y voy a dividir eso por el número total de días del mes. Para conseguirlo, necesito usar una función de búsqueda V y el valor de búsqueda hacia arriba va a ser el mes actual, que es el mes de mi fecha actual. Por lo que mes de C dos acabará de devolver cinco y cinco es el valor look up que busco en esta tabla. Está bien, así que va a buscar cinco, y luego le devolvería el valor en la segunda columna más. Ahí es donde vive mi número de días. Y entonces va a ser una coincidencia exacta. Así que terminalo con un cierre cero. El paréntesis y hit enter ahora regresó 48.4% diciéndome que el 16 de mayo, yo estaba 48.4% del camino a través del mes. Um, y de nuevo, solo para recapitular lo que esta fórmula está haciendo, es calculadora. El número de días han pasado hasta mayo dividido por el número de días en mayo. Y encuentra que al buscar el mes de mi fecha actual hacia abajo en esta matriz de tabla y devolver el valor en la segunda columna más. Entonces esa es esa pieza. La siguiente pieza es todo sobre mi presupuesto mensual y mis gastos mensuales. Entonces el presupuesto mensual es una figura ingresada por el usuario, y eso es lo que quieras. Entonces digamos que tu presupuesto mensual es de 2500 dólares. Eso es sólo una especie de set en piedra ahora para calcular gastos mensuales. Hay un paso que debemos dar porque nuestros datos de gastos están a nivel diario y estamos buscando enrollarlo a nivel mensual. Por lo que necesitamos crear una nueva columna de columna, un mes llamado, que enrolle estos datos al nivel de mes. Entonces solo va a igualar mes del ritmo de la fecha, también, y luego aplicar hacia abajo, y eso me va a mostrar. OK, mes número uno para todas las fechas de enero a para el 3 de febrero para marzo para abril y cinco hasta mayo. Y ahora que tengo esta dimensión adicional, um, um, puedo usar una fórmula de algunos ifs para acumular mis totales de gastos a nivel de mes, no solo a nivel de día. Entonces si recuerdas los algunos ifs de la sección de Funciones estadísticas, solo vamos a empezar con algunos ifs, paréntesis abiertas, y entonces el algunos rango es básicamente donde viven los datos. Eso estoy tratando de agregar es la columna de la Columna de Gastos. Ver mis datos de muestra 10 y luego el rango de criterios es básicamente el número de mes porque ese es el criterio que necesita para cumplir con toe agregar estos totales de gastos por lo que el rango de criterios está llamando A y mi criterio es el mes de la fecha actual. Entonces recuerda lo que voy a necesitar es una función que devuelva el número cinco aquí. Y así sólo van a ser meses de presupuesto mensual acelerando herramienta celda C dos y luego cerrar esos dos paréntesis. Y ahí tienes. Entonces cuando golpeo entrar regresa. 16 03 Lo cual me está diciendo que todos los datos de gastos del mes de mayo suman 2 $1603. Eso puedo comprobar desplazándome hacia abajo a los datos de mayo y sólo mirando al sol 16 03 Así que eso está funcionando correctamente. Um, si quisiera hacer un poco más fáciles estas fórmulas, lo que también podría hacer es sólo en otra celda. Podría simplemente escribir esa función de mes para devolver los cinco y luego en mis algunos ifs, lugar de anidar la función mes allí, simplemente puedo referirme básicamente a esa celda donde ya los han puesto en función y obtener el mismo respuesta. Por lo que algunas formas diferentes de hacerlo. Es algo que depende específicamente de lo que estás buscando hacer ahora. ciento del presupuesto sólo va a ser lo que he gastado dividido por lo que es mi presupuesto Así 64.1%. Um, ahora, aquí verás que he agregado el marco para el gráfico. Yo estoy asi click en eso, y vamos a dar click derecho para seleccionar los datos. Y sé que esta formación no está en gráficos y gráficos, Así que disculpas si esto es difícil de seguir, um, voy a estar poniendo fuera, por supuesto, específicamente cubriendo gráficos y gráficos en un futuro próximo. Por lo tanto, mantente atentos para eso. Pero mientras tanto, solo trata de seguir paso a paso mientras construimos esto. Entonces voy a añadir un Siri. El nombre de Siri se va a llamar por ciento de mes en los valores de un Siri sólo va a ser la celda c por sólo decirme el porcentaje del mes hit. De acuerdo, agrega uno más. De Siri. El nombre de este Siri's va a ser por ciento del presupuesto, y los valores de Siri sólo se van a vender f cuatro it ok, y luego le pegamos. De acuerdo, otra vez. Como puedes ver, está creado gráfico para mí. Eso es mostrar el porcentaje del presupuesto que se muestra en naranja y el por ciento del mes que se muestra en azul . Um, ahora una pieza más que voy a hacer aquí en esta celda, he surgido celda en cada uno a lo que voy a hacer. Básicamente es solo calcular la diferencia entre los dos porcentajes. Entonces sólo va a ser igual a F por menos C cuatro. Entonces eso me está diciendo que he gastado 15.7% puntos por encima de lo que estoy paseando para el mes. Entonces lo que puedo hacer ahora solo para agregar unas campanas y silbatos se va a formato condicional resaltar reglas celulares y decir: Vale, Vale, si este número es mayor que cero, significa que por ciento del presupuesto es mayor que por ciento del mes, lo que significa que estamos más de ritmo, cual es algo malo. Eso formatearé con un rojo claro lleno de texto en rojo oscuro, que es una de las opciones por defecto y pega OK y luego también en una regla donde, si es menor que cero, significa que estaban bajo ritmo, haremos relleno verde con texto verde oscuro y golpeó OK, así que ahora si mi presupuesto mensual era de $5000 ahora los mandemos bajo ritmo por 16.3% puntos para que puedas jugar con él, ya sabes, para ver cómo se actualiza el gráfico y luego durar, pero no menos, si quisiera a Teoh, ya sabes, agregarle algo más de estilo. Podría agregar un operador lógico aquí que diga, uh tan igual. Si H dos es mayor que cero, entonces quiero que esto diga sobre el ritmo. De lo contrario, bajo ritmo, Cerrar el Francis Así que sólo va a agregar una pequeña etiqueta allí bajo mi condicionalmente formateado. Entonces muéstrame bien, estoy bajo ritmo ahora, pero si mi presupuesto de nuevo era de 2500 ahora, estoy más de ritmo. Entonces ahí lo tienes. Se trata de Thea, un gran ejemplo de cómo combinar una serie de funciones diferentes a partir de una serie de categorías de fórmulas diferentes para crear algo que realmente sea útil. Lo último con lo que hablar es que esta herramienta es genial porque se actualizará tipo de por sí misma medida que añades más datos. Entonces digamos como si pasara otra semana. Ya sabes que puedes arrastrar esto hacia abajo ya que puedes ver el relleno del dedo del pie predeterminado de Siri añadiendo días para cada fila. Um, podría simplemente arrastrar mi fórmula de mes hacia abajo y luego decir OK gastó 90 dólares el 17 114 el 18 y 75 el 19 Y cuando salto de nuevo a mi herramienta, como pueden ver, está actualizada. Por lo que ahora la fecha actual es la 19 ya que dos función max. Tengo 58.1% y tengo 75.3%. Ellos son mi factoring presupuestal en esos días adicionales que acabo de agregar. Así que pasamos algún tiempo construyendo fuera del front end, pero ahora es más o menos automatizado usar muy fácilmente avanzando. Por lo que eso termina las funciones de tiempo de citas. Espero que lo hayan encontrado útil. Manténgase atento para la siguiente sección, que es reglas de formato basadas en fórmulas. 11. TRABAJar: funciones de la fecha y el tiempo: Está bien. Enhorabuena por terminar la Sección siete. Funciones de fecha y hora. Si quieres conseguir alguna práctica extra en. Consiguió algunas buenas demos para ti en el archivo de ejercicios de la tarea de Excel. Entonces, adelante y ábrela. Dirígete a la pestaña de funciones de fecha y hora. Y aquí tenemos siete pasos. Esencialmente, vas a practicar usando todas las funciones que cubrimos hoy en este capítulo. Ahora día laborable día laborable, día de red, fin de mes en Phil Siri's. Así que adelante y dale que un archivo de respuesta de disparo esté disponible en el curso. Recurso es o simplemente dispararme un mensaje si necesitas apoyo. Buena suerte.