☰

¿Fórmula de Excel para calcular la fecha en función de 2 celdas y cómo copiarla a otras celdas?

tag-icon • tag-icon microsoft-excel worksheet-function date
¿Fórmula de Excel para calcular la fecha en función de 2 celdas y cómo copiarla a otras celdas?

Usuario novato de Excel aquí. Aquí hay una versión simplificada de mi hoja de cálculo:

start date  end date    eday    duration
                        -21     4
                         12     0

begindate = "03/03/2020"

Quiero crear fórmulas para calcular la fecha de inicio y la fecha de finalización en función de los valores en las columnas eday y duración y la fecha de inicio:

start date = begindate + eday
end date = start date + duration

Entonces, para el ejemplo anterior, si aplico una fórmula, esperaría el siguiente resultado:

start date  end date    eday    duration
02/11/2020  02/11/2020  -21     4
03/15/2020  03/19/2020   12     0

La fecha de inicio 03/03/2020siempre será la misma.

Pude hacer funcionar una fórmula, pero cuando uso el controlador de relleno no copia la fórmula correctamente.

Por favor ayude a un novato con estas preguntas:

  1. ¿Cuál es el mejor lugar para almacenar la fecha de inicio "03/03/2020", una hoja de trabajo separada? codificarlo? ¿otro?

  2. ¿Cuál es la fórmula para la fecha de inicio y la fecha de finalización?

  3. ¿Cómo copio la fórmula en todas las filas de la columna?

Muchas gracias.

Respuesta1

Lo primero que me gusta recordar sobre las fechas y horas en Excel es que en realidad son solo números, que están formateados para que parezcan fechas/horas usando formato numérico. La fecha de hoy (7 de septiembre de 2019) es 43715 y la hora actual (12:20 a. m.) es 0.14302083...

Hay funciones de hoja de cálculo que pueden obtener el número mágico (esencialmente días desde el 1 de enero de 1900), como DATE(), TODAY()o NOW().

Para ello, asumiendo sutodos los diascolumna es Cy sus datos comienzan en fila 2, puede ingresar sufecha de iniciofórmula en A2como =DATE(2020,3,3)+$C2y tufecha finalfórmula en B2como =$A2+$D2. Ése es el enfoque más sencillo.

Tenga en cuenta que he usado referencias de columnas absolutas ( ,, $C) para forzar a Excel a buscar siempre en las columnas y , independientemente de dónde copie/pegue o complete, y he usado referencias de filas relativas ( ) para que, si copio/pego o completar, el número de fila cambiará como parte del llenado (es decir, en la fila , la fórmula se leerá ). A veces quieres esto, a veces no, por lo que debes experimentar y no ver qué hace.$A$DCAD23=$A3+$D3$$

Sospecho que originalmente pusiste tufecha de iniciovalor en una celda, pero usó una referencia relativa en su fórmula y, cuando completó, la referencia relativa para sufecha de iniciocambió. Si estoy en lo cierto, entonces necesitarías una referencia absoluta para tufecha de iniciocelda, por ejemplo, si estaba en la Jfila de la columna 1, su fórmula debería leer $J$1, no J1.

A medida que tenga más confianza, podrá utilizar funciones más complicadas para hacerlo más resistente o configurable. Por ejemplo:

  • Puedes usar elAdministrador de nombrespara almacenar su fecha fija y constante, creando un nuevo nombre llamado BeginDatey estableciendo su valor en =DATE(2020,3,3). Ahora puede actualizar su fórmula de fecha de inicio A2y =BeginDate+$C2ese valor fijo se usará en todas partes.

  • Puede formatear su rango de cuatro columnas como unTabla con encabezados, lo que le permitirá utilizar esos nombres de encabezado en lugar de referencias de columnas. Si formatea el rango como una tabla, entonces en la primera fila de datos de sufecha de iniciocolumna (que era A2) puedes ingresar =BeginDate+[eday]y en tufecha finalcolumna (que era B2) puede ingresar =[start date]+[duration]. Debido a que es una tabla, ambas fórmulas deberían completarse automáticamente a medida que la tabla crece. Ahora aplique un formato numérico simple deCita cortapara ustedfecha de inicioyfecha finalcolumnas.

  • Puede evitar que su tabla muestre fechas calculadas cuando no haya ingresado ningún valor edayo durationcambiando su fórmula enfecha de inicioa los más complicados:

    =IF([eday]="", "", BeginDate+[eday])

    ...y cambiando la fórmula enfecha finala:

    =IF(OR([start date]="",[duration]=""), "", [start date]+[duration])

información relacionada