Repartir un intervalo de fechas por meses en Excel

Marrable Workspace facturó las licencias de mesa de octubre y emitió 12.325,00 €. El mes vale 10.221,78 €. Nada dio error, todos los inquilinos de la hoja existían y todas las cuotas eran correctas: seis de ellos pagaron días que no tuvieron.

La pasada cobró un mes entero a cualquier licencia que tocara octubre. Una mesa alquilada desde el día 26 se facturó a los mismos 980,00 € que una alquilada todo el año, y una licencia que acabó el día 9 se facturó hasta el 31.

El segundo fallo va en sentido contrario, y nadie lo reclamó porque el inquilino no recibió factura. DL-153 es indefinida, así que su fecha de fin está vacía. Una celda vacía es cero, cero como fecha es 1900, y un contrato que acabó en 1900 no factura nada.

Un mes no es una marca sobre un contrato. Es el solape entre dos intervalos de fechas, y un solape es aritmética que cabe en una celda.

Qué necesitas. MIN, MAX, DIA, FIN.MES, CONTAR.SI.CONJUNTO y SUMAPRODUCTO funcionan en todas las versiones de Excel de este siglo y en Google Sheets.

LET, MAP y LAMBDA requieren Microsoft 365 o Excel 2021 y posteriores, y todo lo que hacen aquí se puede hacer también en una columna auxiliar. FIN.MES, FECHA.MES y DIAS.LAB necesitaban el complemento Herramientas para análisis en Excel 2003 y anteriores.

01Doce Mesas, Un Mes, Dos Fallos

La columna C es la fecha de inicio, la D la de fin y la E la cuota mensual. La columna F son los días de octubre que ocupa cada licencia, la G es lo que facturó la pasada y la H es lo que vale la licencia ese mes. El mes está en J1 y J2.

Cuatro licencias ocuparon octubre entero y coinciden: DL-118, DL-124, DL-158 y DL-165. DL-170 empieza el 2 de noviembre y quedó fuera con razón. Las otras siete filas están mal, cada una por un importe distinto.

DL-150 es la fila que deja el fallo a la vista. Es una mesa de apaño de cuatro días a 310,00 € al mes, y se le facturaron 310,00 € por cuatro días de ocupación que valen 40,00 €.

02La Prueba Era Correcta; el Importe No

La pasada de octubre era una sola fórmula, arrastrada por la columna G: =SI(Y(C2<=$J$2;D2>=$J$1);E2;0). Pregunta si la licencia toca octubre y cobra la cuota entera cuando la respuesta es sí.

La mitad de la prueba es correcta y conviene conservarla. Dos intervalos se solapan cuando cada uno empieza en o antes de que acabe el otro. Para un contrato y un mes eso es C2<=$J$2 y D2>=$J$1.

Las dos mitades sostienen el peso: quita la primera y se facturan los contratos de noviembre; quita la segunda y se facturan los de septiembre.

Lo que la fórmula no puede hacer es responder en días. Y devuelve VERDADERO o FALSO, y VERDADERO no es un importe: cuando alguien necesitó una cifra, la cuota mensual era el único número a mano.

03La Fórmula del Solape: MIN, MAX y el +1

Una sola fórmula da los días que un contrato ocupa de un mes, y es la que hay que aprender:

=MAX(0;MIN(D2;$J$2)-MAX(C2;$J$1)+1)

MIN(D2;$J$2) es la más temprana entre el fin del contrato y el fin del mes: la que corta antes la facturación. MAX(C2;$J$1) es el más tardío de los dos inicios: el que la empieza. Entre las dos recortan el contrato al mes.

Pasa DL-150 por ahí. La licencia va del 6 al 9 de octubre, así que MIN devuelve el 9 de octubre y MAX el 6:

MIN(09/10/2026; 31/10/2026)  = 09/10/2026
MAX(06/10/2026; 01/10/2026)  = 06/10/2026
09/10 - 06/10 + 1            = 4 días

El +1 no es un apaño: es el criterio de facturación. Una licencia que empieza y acaba el mismo día se cobra un día, no cero, así que las dos fechas extremas están dentro del periodo.

Quítalo y todas las filas del archivo se quedan un día cortas, de forma consistente, que es el tipo de error que cuadra consigo mismo durante años.

El MAX(0;...) exterior se ocupa de las filas que no están en el mes. DL-170 empieza el 2 de noviembre, así que al recortar el fin queda antes del inicio, la resta sale negativa y sin la protección la hoja abonaría al inquilino dos días en negativo.

04Prorrateo por Días, y Con Qué Denominador

Los días se convierten en dinero dividiendo por la longitud del mes. Con los días de solape en la columna F:

=REDONDEAR(E2*F2/DIA($J$2);2)

DIA($J$2) lee el número de día de la última fecha del mes, así que octubre devuelve 31, noviembre 30 y febrero de 2028 devuelve 29. Eso es mejor que escribir 30 o 365/12, porque la fórmula sigue el calendario en vez de una suposición sobre él.

Hay más de una base defendible, eso sí, y no coinciden. DL-136 ocupa seis días de octubre a 980,00 € al mes:

La base es una condición del contrato, no una preferencia de fórmula. Elige la que dice la licencia, ponla en una celda con nombre y las cifras mensuales cuadrarán con lo que se le dijo al inquilino que estaba comprando.

Redondea una sola vez, en la fila, a céntimos. REDONDEAR(...;2) mantiene la factura y el libro mayor en el mismo número. Sin él, las partes llevan fracciones de céntimo que acaban en un total de control uno o dos céntimos desviado y sin fila a la que culpar.

05Una Fecha de Fin Vacía No Factura Nada

DL-153 no tiene fecha de fin porque no tiene fin: dura hasta que alguien avise. La celda está vacía, y una celda vacía en aritmética es cero.

Así que MIN(D2;$J$2) compara el fin de mes con cero y devuelve cero, que como fecha es el 0 de enero de 1900. El solape sale en torno a menos 46.000 días, MAX(0;...) lo deja en cero y el contrato indefinido no factura nada. En septiembre tampoco facturó: 2.300,00 € entre los dos meses.

Protege la fecha de fin donde la lees, en vez de parchear el número después:

=MAX(0;MIN(SI(D2="";$J$2;D2);$J$2)-MAX(C2;$J$1)+1)

Una licencia indefinida que sigue viva tiene, a efectos de este mes, una fecha de fin igual al fin de mes. SI(D2="";$J$2;D2) dice exactamente eso, y seguirá funcionando todos los meses siguientes sin que nadie edite la hoja.

La alternativa es prohibir el vacío: pon una fecha lejana como el 31 de diciembre de 2099 en la columna y usa validación de datos para exigir una fecha. Cualquiera de las dos vale; lo que no se sostiene es una columna donde vacío significa a veces «para siempre» y a veces «nadie lo ha rellenado».

06MIN y MAX Agregan, Así Que las Filas Necesitan Columna

La tentación siguiente es saltarse la columna auxiliar y totalizar el mes en una sola celda. No funciona, y falla en silencio:

=SUMAPRODUCTO(E2:E13*(MIN(D2:D13;$J$2)-MAX(C2:C13;$J$1)+1)/DIA($J$2))

MIN y MAX son funciones de agregado. Si les das una columna entera devuelven un número para todo el conjunto: MIN(D2:D13;$J$2) es la fecha de fin más temprana del archivo —cero, gracias a DL-153— y MAX(C2:C13;$J$1) es el 2 de noviembre de DL-170. Todas las filas reciben el mismo recuento absurdo.

Hay dos arreglos. El primero es la columna auxiliar, que además es la versión que otra persona puede auditar:

=SUMAPRODUCTO(E2:E13;F2:F13)/DIA($J$2)

El segundo es MAP, que aplica la fórmula de fila a cada fila por turno y suma el resultado, sin columnas extra en la hoja:

=SUMA(MAP(C2:C13;D2:D13;E2:E13;LAMBDA(i;f;c;
  c*MAX(0;MIN(SI(f="";$J$2;f);$J$2)-MAX(i;$J$1)+1)/DIA($J$2))))

Cualquier fórmula que envuelva una columna entera en MIN o MAX y espere una respuesta por fila está agregando el archivo sin querer, y nada en el resultado lo dice. El número vuelve con formato de moneda y se queda en la celda con toda la pinta de un total.

07Una Sola Fórmula para Doce Meses

Un mes es una columna. Un año es la misma fórmula con los meses en la fila de arriba, y ahí es donde las referencias mixtas se ganan el sueldo. Pon el día 1 de cada mes en M1 a X1 y, en M2:

=MAX(0;MIN($D2;FIN.MES(M$1;0))-MAX($C2;M$1)+1)

$C2 y $D2 fijan la columna y dejan viajar la fila; M$1 fija la fila y deja viajar la columna. Arrastra el bloque a la derecha y hacia abajo y cada contrato queda repartido por todos los meses que toca, con cero en los que no.

Para dinero en vez de días, envuélvelo igual que antes y deja que FIN.MES dé tanto el fin de mes como la longitud del mes:

=REDONDEAR($E2*MAX(0;MIN($D2;FIN.MES(M$1;0))-MAX($C2;M$1)+1)/DIA(FIN.MES(M$1;0));2)

La cuadrícula trae su propia comprobación, que es la razón para construirla así. Los días de un contrato a lo largo de toda la cuadrícula deben sumar la duración del propio contrato, así que =SUMA(M2:X2) y =D2-C2+1 coinciden en toda fila que empiece y acabe dentro de la ventana.

08Contar lo Que Está Vivo, No lo Que Empezó

La ocupación se fue por el mismo camino que el dinero. El panel contaba los contratos cuya fecha de inicio caía dentro del mes:

=CONTAR.SI.CONJUNTO($C$2:$C$13;">="&$J$1;$C$2:$C$13;"<="&$J$2)

Eso devuelve 4, y es una cifra verdadera sobre otra pregunta: en octubre se firmaron cuatro licencias. Once estuvieron ocupadas en algún momento del mes, y los dos números nunca tuvieron que coincidir.

Los contratos vivos son una prueba de solape, no un filtro de fechas:

=SUMAPRODUCTO(($C$2:$C$13<=$J$2)*(SI($D$2:$D$13="";$J$2;$D$2:$D$13)>=$J$1))

Sin proteger, esa cuenta devuelve 10 en vez de 11, porque la fecha de fin vacía falla la prueba igual que falló la facturación. El SI es la misma protección de la sección 5, y corresponde a toda fórmula que lea la fecha de fin.

Y si lo que quieres son mesas en vez de contratos, promedia los días. Las doce licencias ocupan 230 días de un mes de 31, así que =SUMA(F2:F13)/DIA($J$2) devuelve 7,42 mesas en uso de media a lo largo de octubre.

09Días Laborables en Vez de Días Naturales

El trabajo a tarifa diaria —autónomos, alquiler de maquinaria, sustituciones— se factura por días laborables, y entonces el denominador también cambia. DIAS.LAB cuenta las dos fechas extremas, así que no hay +1 que sumar:

=MAX(0;DIAS.LAB(MAX(C2;$J$1);MIN(SI(D2="";$J$2;D2);$J$2);Festivos))

Octubre de 2026 tiene 22 días laborables frente a 31 naturales, y las dos bases separan las filas de mitad de mes. Un periodo que cruza dos fines de semana pierde días; uno que cae de lunes a viernes gana cuota de mes.

DL-150 es el caso más claro. Cuatro días que caen de martes a viernes son cuatro días naturales y cuatro laborables, y aun así 4/22 de mes es más cuota que 4/31: 16,36 € más en esta licencia.

10Cuando el Mes Completo Es la Respuesta Correcta

No todos los contratos se prorratean. La facturación por aniversario cobra un mes completo desde la fecha de inicio, así que una licencia que empieza el 12 de octubre factura el día 12 de cada mes, y prorratearla es una respuesta equivocada, no un refinamiento.

Cuenta meses en vez de días cuando el acuerdo sea ese. =SIFECHA(C2;D2+1;"m") da los meses completos transcurridos, y los meses tocados son aritmética sobre las partes:

=(AÑO(D2)-AÑO(C2))*12+MES(D2)-MES(C2)+1

Para la siguiente fecha de cobro, FECHA.MES resuelve el problema de fin de mes que sumar 30 días no resuelve. =FECHA.MES(C2;1) sobre el 31 de enero devuelve el 28 de febrero, y sobre el 31 de octubre devuelve el 30 de noviembre, porque ajusta al final del mes más corto.

El criterio es una condición del contrato, así que las dos poblaciones no deberían compartir fórmula. Mantén una columna de base en K —Prorrateo o Mes completo— y decide con ella: =SI(K2="Mes completo";E2;REDONDEAR(E2*F2/DIA($J$2);2)).

11Dos Reservas, Una Mesa

La misma prueba de solape encuentra recursos reservados dos veces, que es la otra cosa que esconde un par de columnas de fechas. Para cada fila, cuenta las filas de la misma mesa cuyas fechas se solapan con ella:

=SUMAPRODUCTO(($B$2:$B$13=B2)*($C$2:$C$13<=D2)*($D$2:$D$13>=C2))>1

Toda fila se cruza consigo misma, así que la cuenta es 1 en una reserva limpia y 2 o más en un conflicto. En esta hoja devuelve VERDADERO dos veces, las dos en la mesa D-04: DL-147 llega hasta el 23 de octubre y DL-161 empieza el 19.

Son cinco días, del 19 al 23 de octubre, con una mesa vendida a dos inquilinos. =MAX(0;MIN(D7;D11)-MAX(C7;C11)+1) es otra vez la fórmula del solape, apuntada a dos contratos en vez de a un contrato y un mes, y devuelve 5.

Las fechas de fin vacías rompen esta comprobación igual que rompieron la facturación, así que protégelas también aquí: SI($D$2:$D$13="";$J$2;$D$2:$D$13) dentro de la tercera prueba. Un contrato indefinido se solapa con todo lo posterior a su inicio, que es justo lo que debe decir un informe de conflictos.

12Las Comprobaciones Que lo Cazan Antes de Facturar

Cinco celdas encima de la tabla habrían cazado octubre antes de que saliera, y cada una es una pregunta con una respuesta que ya conoces.

1. ¿Cuadra el mes por dos caminos? =SUMA(H2:H13) devuelve 10.221,78 € y =SUMAPRODUCTO(E2:E13;F2:F13)/DIA($J$2) devuelve 10.221,77 €. Un céntimo de diferencia es el redondeo que elegiste en las filas; euros de diferencia es una fila donde los días y el dinero no se hablan.
2. ¿Puede una fila pasarse del mes? =SUMAPRODUCTO(--(F2:F13>DIA($J$2))) tiene que dar cero. Cualquier otra cosa significa una fecha que debía recortarse y no se recortó.
3. ¿Hay algún fin anterior a su inicio? =SUMAPRODUCTO((D2:D13<>"")*(D2:D13<C2:C13)) tiene que dar cero, y es la comprobación que caza un año mal escrito.
4. ¿Son fechas las fechas? =SUMAPRODUCTO(--ESTEXTO(C2:C13))+SUMAPRODUCTO(--ESTEXTO(D2:D13)) tiene que dar cero. El texto se ordena por encima de cualquier fecha real, así que MIN elige el texto y la fila factura un mes entero.
5. ¿Cuántos vacíos hay? =SUMAPRODUCTO(--(D2:D13="")) devuelve 1 aquí, que es DL-153 y es deliberado. La cifra solo es segura cuando sabes cuál es.

Las filas de mes completo son las que hay que cuadrar contra la cuota. Cinco contratos ocupan octubre entero, =CONTAR.SI.CONJUNTO(F2:F13;DIA($J$2)) lo confirma, y cada uno de esos cinco debe facturar su cuota exacta. Que uno salga a 1.045,16 € en vez de 1.620,00 € significa que el recorte se ha torcido en el otro sentido.

13Diez Cosas Que Muerden

1. Una fecha de fin vacía es cero, no infinito. MIN la elige, el solape sale negativo y el contrato no factura nada.
2. El +1 es un criterio de facturación. Si lo quitas, todas las filas se quedan un día cortas, de forma consistente e invisible.
3. MIN y MAX agregan. Sobre una columna devuelven un número para todo el archivo, no uno por fila.
4. Un filtro por fecha de inicio no es una prueba de vigencia. CONTAR.SI.CONJUNTO sobre el inicio responde «firmados este mes», que es otro número.
5. DIA($J$2) es la longitud del mes. Escribir 30 a mano cobra de más once meses al año en un contrato de base 30.
6. Las fechas de texto ganan toda comparación. Un "01/10/2026" escrito como texto supera a cualquier fecha real en un MIN, y la fila factura el mes completo.
7. Redondea una vez, a céntimos, en la fila. Sin redondear, el cuadre se va uno o dos céntimos y nadie encuentra qué fila lo hizo.
8. Las referencias mixtas hacen la cuadrícula. $C2 y M$1, no C2 y M1, o el arrastre se desparrama por el bloque.
9. DIAS.LAB ya cuenta los dos extremos. Sumarle +1 factura un día que no existe.
10. Prorratear un contrato de mes completo también es un error. La base va en una columna, no en una suposición.

14Mini Ejercicios

1. Reconstruye la columna rota. Escribe =SI(Y(C2<=$J$2;D2>=$J$1);E2;0) por la columna G de la hoja de ejemplo y comprueba que totaliza 12.325,00 €. Ese es el número que salió.
2. Recórtalo. Pon =MAX(0;MIN(D2;$J$2)-MAX(C2;$J$1)+1) en la columna F y =REDONDEAR(E2*F2/DIA($J$2);2) en la H, y cuadra el total a 10.221,78 €.
3. Encuentra la fila indefinida. Anota qué factura DL-153 antes de proteger el vacío, luego añade SI(D2="";$J$2;D2) y mira llegar los 1.150,00 €.
4. Cuenta de tres maneras. Calcula licencias firmadas, licencias vivas y mesas de media en octubre, y comprueba que te salen 4, 11 y 7,42.
5. Caza el conflicto. Añade la columna de solape por mesa, encuentra la pareja de D-04 y calcula cuántos días de octubre se vendieron dos veces.

Para Quedarte Con Esto

La hoja no estaba rota. Hizo una pregunta de verdadero o falso, obtuvo una respuesta verdadera y luego usó una cuota mensual como si respondiera «cuánto mes es esto», una pregunta que nadie había escrito en ningún sitio.

Escribirla cuesta una celda. =MAX(0;MIN(D2;$J$2)-MAX(C2;$J$1)+1) es la idea completa: recorta el contrato al mes por los dos extremos, cuenta los días con los dos incluidos y deja el resultado en cero como mínimo. Todo lo demás de aquí es esa fórmula con otro denominador u otro par de rangos.

Y protege el vacío. Una fecha de fin vacía es la celda más cara de una tabla de contratos, porque se lee como una fecha de 1900 y falla en la dirección por la que nadie reclama.