☰

¿Usar HOY en Excel para calcular la siguiente instancia de una fecha dentro de una cierta cantidad de meses?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
¿Usar HOY en Excel para calcular la siguiente instancia de una fecha dentro de una cierta cantidad de meses?

Edite para aclarar: la fecha de devolución DEBE ser inferior a 24 meses a partir de la fecha actual porque es una fecha de vencimiento y los artículos deben caducar en el momento correcto a menos que se renueven. Otra forma de decirlo es que la fecha debe ser lo más cercana posible a un ciclo de renovación de 2 años sin exceder los 2 años.

Publicación original: ¡Buenos días! He estado buscando en Google y no he llegado a ninguna parte, así que espero poder ayudar a crear una fórmula. Necesito crear una fórmula única que no haga referencia a otras celdas porque espero limitar la cantidad de errores de otros usuarios :)

Lo que necesito: encontrar la siguiente instancia del 31/03 O 30/09 que sea mayor de 18 meses pero menor de 24 meses a partir de HOY.

Según mi redacción anterior, siento que una combinación de HOY, O, <, > y un identificador de mes/día me daría lo que estoy buscando, pero tengo dificultades con el orden de las operaciones. Actualmente, mis compañeros de trabajo utilizan una tabla (a continuación) para calcular manualmente estas fechas y es tedioso.

  • Abril - Impar de septiembre = Próximo impar de marzo
  • Abril - Par de septiembre = Próximo par de marzo
  • Oct Par - Mar Impar = Septiembre Próximo Par
  • Impar de octubre - Par de marzo = Próximo impar de septiembre

Gracias de antemano por cualquier aporte y editaré esto si se me ocurre una fórmula aproximada después del trabajo.

Respuesta1

A continuación se presenta una solución sin matrices, basada en fórmulas y fácil de entender que funciona con Excel 2010 (y versiones anteriores). Utiliza columnas auxiliares (que se pueden ocultar).

Dado que no siempre se puede cumplir el requisito de <24 meses y >18 meses, y el requisito estricto es <24 meses, aflojé el requisito en el otro extremo a >=18 meses.

Para cualquier fecha determinada, solo hay tres fechas objetivo posibles: 31/3 o 30/9 en el año 18 meses a partir de la fecha, o 31/3 el año siguiente. Sólo necesitas seleccionar el primero de los que cumpla con los criterios.

ingrese la descripción de la imagen aquí

La pregunta especifica los resultados basados ​​en HOY. También quería mostrar cómo se comporta esto en otros "hoyes". La celda A2 contiene =TODAY(). Las otras celdas de la columna A son sólo algunas otras fechas a modo de ilustración; especialmente aquellos en las "fechas límite" relacionadas con el 31/3 y el 30/9. Las fórmulas hacen referencia a la celda de fecha, pero TODAY() podría codificarse de forma rígida.

Las columnas I:J son sólo para ilustración. Muestran las fechas 18 y 24 meses de la columna Una fecha para ayudar a comprender por qué se seleccionan los valores de los resultados.

Las columnas auxiliares son C:E. Estos contienen las tres fechas objetivo candidatas para la fecha de la columna A. El objetivo 1 en C2 contiene:

=DATE(YEAR(EDATE(A2,18)),3,31)

Esto crea la fecha 31/03 en el año 18 meses a partir de la fecha de la columna A. El objetivo 2 en D2 contiene:

=DATE(YEAR(EDATE(A2,18)),9,30)

Esto crea la fecha 30/9 en el año 18 meses a partir de la fecha de la columna A. El objetivo 3 en E2 contiene:

=DATE(YEAR(EDATE(A2,18))+1,3,31)

Esto crea la fecha 31/03 en el año siguiente a los 18 meses de la fecha de la columna A.

El resultado está en la columna G. La fórmula en G2:

=SUMPRODUCT((C2:E2<EDATE(A2,24))*(C2:E2>=EDATE(A2,18))*C2:E2)

Debido a los requisitos, solo calificará una fecha objetivo. SUMPRODUCT maneja las comparaciones de matrices con una fórmula normal (sin matriz).

C2:E2<EDATE(A2,24)devuelve VERDADERO/FALSO (1/0) para cada fecha objetivo en función de si la fecha está a menos de 24 meses de la fecha de la columna A.

C2:E2>=EDATE(A2,18)de manera similar, devuelve 1/0 para cada fecha objetivo en función de si la fecha es >= 18 meses desde la fecha de la columna A.

Solo una fecha objetivo cumplirá ambas condiciones, por lo que el producto de esos valores 1/0 será 1para esa fecha y 0para las otras dos fechas. Ese producto se multiplica por el valor en cada celda de fecha objetivo. Dado que las fechas se almacenan como números, el resultado es el número que representa la fecha objetivo calificada. Sólo hay que formatearlo como una fecha.

Respuesta2

La siguiente función definida por el usuario primero crea un período de calendario de 18 a 24 meses a partir de hoy. Luego recorre el intervalo hasta que encuentra una fecha que coincide con sus criterios:

Public Function ProjDate() As Date
    Dim d1 As Date, d2 As Date, y As Long
    Dim dd As Date, d As Long, m As Long

    d = Day(Date)
    m = Month(Date)
    y = Year(Date)
    d1 = DateSerial(y, m + 18, d + 1)
    d2 = DateSerial(y, m + 24, d - 1)

    For dd = d1 To d2
        d = Day(dd)
        m = Month(dd)
        If (m = 3 And d = 31) Or (m = 9 And d = 30) Then
            ProjDate = dd
            Exit Function
        End If
    Next dd
End Function

ingrese la descripción de la imagen aquí

Las funciones definidas por el usuario (UDF) son muy fáciles deinstalary use:

  1. ALT-F11 abre la ventana VBE
  2. ALT-I ALT-M abre un módulo nuevo
  3. pegue las cosas y cierre la ventana de VBE

Si guarda el libro de trabajo, la UDF se guardará con él. Si está utilizando una versión de Excel posterior a 2003, debe guardar el archivo como .xlsm en lugar de .xlsx.

Aeliminarla UDF:

  1. abrir la ventana VBE como arriba
  2. borrar el código
  3. cerrar la ventana VBE

Ausarla UDF de Excel:

=mifunción(A1)

Para obtener más información sobre las macros en general, consulte:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

y

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

y para obtener detalles sobre las UDF, consulte:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

¡Las macros deben estar habilitadas para que esto funcione!

Respuesta3

Aquí tienes una fórmula. Tal como está escrito, para pruebas, hace referencia A1. Sin embargo, puede reemplazarlo A1con TODAY()si encuentra que devuelve los resultados esperados:

=MAX((MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))-1

Lo anterior es unformaciónfórmula.

Dado que se trata de una fórmula matricial, debes "confirmarla" manteniendo presionado ctrl+ shiftmientras presionas enter. Si hace esto correctamente, Excel colocará llaves {...}alrededor de la fórmula como se observa en la barra de fórmulas.

Si quieres evitar el CSEtrámite de entrada, puedes probar el un poco más largo:

=AGGREGATE( 14,4,(MONTH(EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}))={4,10})*EDATE(A1-DAY(A1)+1,{18;19;20;21;22;23;24}),1)-1

información relacionada