funciones - todas las formulas de excel
Identificar las fechas que corresponden al mismo mes, el período del día a través de los años (1)
así que tengo una lista completa de las fechas. Necesito averiguar qué fechas caen dentro del mismo rango (día y mes) que mi "Fecha de inicio" y "Fecha de finalización" estipuladas para cada uno de los años.
por ejemplo, para la Fecha de inicio: 2/12/2004, Fecha de finalización: 31/1/2005, fechas que caen entre el 2/12/2000 y el 31/01/2001, el 12/12/2001 y el 31/1/2002, 2 / 12/2001 y 31/1/2002 etc. deben marcarse como "Válido".
actualmente utilizo un límite superior / inferior y luego pruebo si la Fecha cae dentro del rango.
Fórmulas de Excel:
Superior: = FECHA (AÑO (Fecha), MES (Prueba), DÍA (Prueba) + Período de Atrás)
Inferior: = FECHA (AÑO (Fecha), MES (Prueba), DÍA (Prueba) -Revisar Período)
Válido: = IF (AND (Fecha <= Upper, Date> = Lower), 1,0)
Test 1/1/2005
Look Back Period (Days) 30
Start Date 2/12/2004 (=Test + Look Back Period)
End Date 31/1/2005 (=Test - Look Back Period)
Date Upper Lower Valid
04/01/2000 31/1/2000 2/12/1999 1
05/01/2000 31/1/2000 2/12/1999 1
06/01/2000 31/1/2000 2/12/1999 1
07/01/2000 31/1/2000 2/12/1999 1
10/01/2000 31/1/2000 2/12/1999 1
11/01/2000 31/1/2000 2/12/1999 1
12/01/2000 31/1/2000 2/12/1999 1
13/01/2000 31/1/2000 2/12/1999 1
14/01/2000 31/1/2000 2/12/1999 1
17/01/2000 31/1/2000 2/12/1999 1
18/01/2000 31/1/2000 2/12/1999 1
19/01/2000 31/1/2000 2/12/1999 1
20/01/2000 31/1/2000 2/12/1999 1
21/01/2000 31/1/2000 2/12/1999 1
24/01/2000 31/1/2000 2/12/1999 1
25/01/2000 31/1/2000 2/12/1999 1
26/01/2000 31/1/2000 2/12/1999 1
27/01/2000 31/1/2000 2/12/1999 1
28/01/2000 31/1/2000 2/12/1999 1
31/01/2000 31/1/2000 2/12/1999 1
01/02/2000 31/1/2000 2/12/1999 0
02/02/2000 31/1/2000 2/12/1999 0
03/02/2000 31/1/2000 2/12/1999 0
04/02/2000 31/1/2000 2/12/1999 0
07/02/2000 31/1/2000 2/12/1999 0
.
.
.
30/11/2000 31/1/2000 2/12/1999 0
01/12/2000 31/1/2000 2/12/1999 0
04/12/2000 31/1/2000 2/12/1999 0 <-- :(
05/12/2000 31/1/2000 2/12/1999 0 <-- :(
06/12/2000 31/1/2000 2/12/1999 0 <-- :(
07/12/2000 31/1/2000 2/12/1999 0 <-- :(
08/12/2000 31/1/2000 2/12/1999 0 <-- :(
11/12/2000 31/1/2000 2/12/1999 0 <-- :(
12/12/2000 31/1/2000 2/12/1999 0 <-- :(
13/12/2000 31/1/2000 2/12/1999 0 <-- :(
14/12/2000 31/1/2000 2/12/1999 0 <-- :(
15/12/2000 31/1/2000 2/12/1999 0 <-- :(
18/12/2000 31/1/2000 2/12/1999 0 <-- :(
19/12/2000 31/1/2000 2/12/1999 0 <-- :(
20/12/2000 31/1/2000 2/12/1999 0 <-- :(
21/12/2000 31/1/2000 2/12/1999 0 <-- :(
22/12/2000 31/1/2000 2/12/1999 0 <-- :(
25/12/2000 31/1/2000 2/12/1999 0 <-- :(
26/12/2000 31/1/2000 2/12/1999 0 <-- :(
27/12/2000 31/1/2000 2/12/1999 0 <-- :(
28/12/2000 31/1/2000 2/12/1999 0 <-- :(
29/12/2000 31/1/2000 2/12/1999 0 <-- :(
01/01/2001 31/1/2001 2/12/2000 1
02/01/2001 31/1/2001 2/12/2000 1
03/01/2001 31/1/2001 2/12/2000 1
04/01/2001 31/1/2001 2/12/2000 1
05/01/2001 31/1/2001 2/12/2000 1
08/01/2001 31/1/2001 2/12/2000 1
09/01/2001 31/1/2001 2/12/2000 1
10/01/2001 31/1/2001 2/12/2000 1
11/01/2001 31/1/2001 2/12/2000 1
La fórmula funciona bien siempre que los períodos no se crucen entre años. Sin embargo, en caso de que el límite superior e inferior se crucen entre 2 años diferentes, la fórmula válida excluirá los valores del año anterior.
¿Hay alguna solución para esto?
Para hacer esto con fórmulas, configure dos columnas más de ayuda
lower1: =DATE(YEAR(Date)-1,MONTH(StartDate),DAY(StartDate))
upper1: =C8+2*LookBack
Lower2: =DATE(YEAR(Date),MONTH(StartDate),DAY(StartDate))
Upper2: =Lower2+2*LookBack
valid: =IF(OR(AND(Date>=lower1,Date<=upper1),AND(Date>=Lower2,Date<=Upper2)),1,0)
Todo lo que estamos haciendo es establecer dos conjuntos de límites. En relación con la Date , uno comienza con el año anterior; el otro comienza con el año actual. Luego marque Date para ver si cae dentro de cualquiera de los conjuntos.
Para las apariencias, no es necesario que las columnas de límites sean visibles, ni siquiera que sean contiguas con la Date y valid columnas valid .
