01 / 14Diez Meses Cortos, Dos Meses Exactos

Funciones de FechaSUMAR.SI.CONJUNTOExcel

Fechas con hora en Excel: arreglar SUMAR.SI.CONJUNTO

La Refacturación de Portes Se Quedó 37.204,60 € Corta en un Año y Dos Meses Cuadraron al Céntimo, Porque Cada Fecha del Volcado Llevaba una Hora

23 oct 202615 min de lectura

UsaSUMAR.SI.CONJUNTOCONTAR.SI.CONJUNTOFIN.MESRESIDUO

Marlowe Freight refactura portes a un cliente minorista todos los meses, y durante un año la hoja cuadró. La nave transportó 1.196.344,50 €. Al cliente se le facturaron 1.159.139,90 €. Nada estaba en rojo.

La diferencia son 37.204,60 €, y toda ella es un día al mes. Diez de los doce meses se quedaron cortos exactamente en lo que Kirkburn transportó el último día. Los otros dos salieron bien al céntimo.

Las fechas del volcado no eran fechas. Cada marca llevaba una hora del día, la columna tenía formato dd/mm/aaaa, y ninguna celda de la hoja enseñaba un reloj.

La refacturación del añoImporte
Transportado en Kirkburn1.196.344,50 €
Refacturado al cliente1.159.139,90 €
Nunca facturado37.204,60 €
Meses cortos10 de 12
Dentro de los 90 días11.010,90 €

Alcance. SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, FIN.MES, RESIDUO y ENTERO, más la agrupación de tablas dinámicas, el autofiltro y Power Query, en Excel para Microsoft 365, 2021, 2019 y Excel para Mac.

Una fecha en Excel es un número de días y la hora es la parte decimal. Todo lo que sigue se deduce de ese único hecho, y nada depende del sistema de fechas del libro.

En todo el artículo, la columna B guarda la marca de entrega, la columna F el porte neto y H2 el primer día del mes que se refactura.

01Diez Meses Cortos, Dos Meses Exactos

Marlowe Freight mueve sesenta vehículos desde Kirkburn y refactura los portes a su mayor cliente minorista cada mes. La hoja tiene una fila por mes, y cada fila es un SUMAR.SI.CONJUNTO sobre el volcado de los terminales.

El volcado trae una línea por envío: una referencia, una marca de entrega, un peso y un importe neto. Poco menos de cuatro mil filas al mes, pegadas debajo de las del mes anterior.

La fórmula acota el mes entre su primer día y el último: =SUMAR.SI.CONJUNTO(F:F;B:B;">="&H2;B:B;"<="&FIN.MES(H2;0)). Se lee igual que lo que pretende hacer, y se equivoca en un día todos los meses.

Diez de los doce meses salieron cortos. Agosto y noviembre no, porque el 31 de agosto y el 30 de noviembre de 2025 cayeron en domingo, Kirkburn no recoge en domingo, y un mes sin nada en su último día no tiene nada que perder.

La conciliación de agosto cuadró al céntimo. Se hizo en septiembre contra la cifra de la propia nave, coincidió exacta, y nadie volvió a abrir los otros once meses en medio año.

Un Año de Refacturaciones, Diez de Ellas un Día Cortas

La hoja de refacturación de portes de Marlowe Freight para un cliente minorista, de abril de 2025 a marzo de 2026, en A1:F15. La columna C es lo que el propio volcado del escáner dice que transportó la nave en el mes, y la columna D lo que devolvió la fórmula y se facturó al cliente. La columna E es la diferencia, que en todos los casos es exactamente lo que se transportó el último día del mes. Las dos filas donde C y D coinciden son los dos meses cuyo último día cayó en domingo; las dos filas del final son el año y la parte que sigue dentro de la ventana de noventa días del contrato.

ABCDEF
1
Month
Last day
Carried
Recharged
Missed
What happened
2
Apr 2025
Wed 30 Apr
98412.5
94880.3
3532.2
The first month on the new export. One hundred and twelve consignments went out on the Wednesday and none of them reached the invoice, because the fence closed at midnight on the 30th.
3
May 2025
Sat 31 May
101860.4
98945.8
2914.6
A Saturday, which Kirkburn works and the client is billed for. Saturdays are quieter than weekdays, so this is the smallest loss of the ten and the one least likely to be queried.
4
Jun 2025
Mon 30 Jun
96274.8
92155.9
4118.9
A Monday, and Mondays clear the weekend backlog. The last day of a month that ends on a Monday is one of the heaviest days the depot runs, which is why June lost more than May on fewer trading days.
5
Jul 2025
Thu 31 Jul
104593.2
100732.8
3860.4
An ordinary Thursday. Nothing about this row looks wrong on the schedule: the recharge is in the same band as every other month, and the shortfall is inside the month-to-month noise.
6
Aug 2025
Sun 31 Aug
92158.6
92158.6
0
The 31st was a Sunday and Kirkburn does not collect on a Sunday, so the month had nothing on its last day to lose. This is the month the September reconciliation sampled, and it footed to the penny.
7
Sep 2025
Tue 30 Sep
107430.1
103222.6
4207.5
The month after the reconciliation passed. Confidence in the schedule was at its highest here and the quarter closed on a figure that was over four thousand pounds light.
8
Oct 2025
Fri 31 Oct
110864.3
106272.5
4591.8
A Friday, the depot's heaviest day, and the second largest loss in the year. The busier the last day of the month, the more the fence throws away, and month ends land on a Friday twice a year.
9
Nov 2025
Sun 30 Nov
99742
99742
0
The second Sunday month end of the year, and the second month that footed. Two exact months out of twelve is enough to make the schedule look sound to anybody checking one at a time.
10
Dec 2025
Wed 31 Dec
88316.7
85348.4
2968.3
New Year's Eve, a half day, and the quietest month end of the twelve. The depot ran until two o'clock and every one of those consignments was stamped after midnight on the 31st.
11
Jan 2026
Sat 31 Jan
94508.9
91404.2
3104.7
The first of the three months still inside the client's ninety-day re-billing window when the fault was found, and therefore the first of the three that can still be invoiced.
12
Feb 2026
Sat 28 Feb
90137.4
86881.3
3256.1
A short month ending on a Saturday. The missed day is a smaller share of a smaller month, which is exactly the shape that keeps a percentage check quiet.
13
Mar 2026
Tue 31 Mar
112045.6
107395.5
4650.1
The year's busiest month end: 148 consignments between 06:12 and 19:48, worth £4,650.10, every one of them stamped with a time and none of them inside the fence.
14
The year
12 months
1196344.5
1159139.9
37204.6
What the depot carried for this client against what the client was invoiced. Ten of the twelve months are short, the two exact months both ended on a Sunday, and the gap is 3.11% of the year.
15
Still re-billable
3 months
296691.9
285681
11010.9
January, February and March, the only months still inside the ninety-day window in the carriage agreement. The other £26,193.70 is time-barred and will not be recovered.

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

02Una Celda de Fecha Puede Llevar una Hora Invisible

Excel guarda una fecha como un número entero de días y la hora como la parte decimal. 31/03/2026 es 46112, y 31/03/2026 16:42 es 46112,695833.

Una celda con formato dd/mm/aaaa muestra las dos como 31/03/2026. El formato redondea lo que ves; nunca redondea lo que la celda guarda, y cada criterio que escribes se compara contra lo que guarda.

Así que una columna que parece un año de fechas limpias puede contener cuatro mil números distintos sin apenas uno entero entre ellos.

Lo que preguntasEn una fecha limpiaEn una marca de 16:42
Lo que muestra la celda31/03/202631/03/2026
=B2=FECHA(2026;3;31)VERDADEROFALSO
=RESIDUO(B2;1)00,695833
=TEXTO(B2;"hh:mm")00:0016:42

Escenario: Pon =TEXTO(B2;"dd/mm/aaaa hh:mm") al lado de la primera marca de tu volcado y arrástralo diez filas. Cualquier cosa distinta de 00:00 al final de la línea es una fila que tu total de fin de mes va a tirar.

Pruébalo en la cuadrícula

03Una Celda Cuenta Todas las Marcas con Hora

Una sola fórmula te dice si tienes este problema antes de ponerte a buscarlo. =SUMAPRODUCTO(--(RESIDUO(B2:B4000;1)>0)) cuenta las celdas de la columna de fechas cuya parte decimal no es cero.

En el volcado de Kirkburn devuelve 3.964, que son todas las filas del fichero. En una hoja tecleada a mano devuelve 0, y ninguna de las vallas de abajo importa nunca.

La respuesta peligrosa es la intermedia. Significa que dos orígenes caen en la misma columna, y el único que tus criterios pueden ver es el que escribe medianoche.

ComprobaciónFórmulaKirkburn
Filas con hora=SUMAPRODUCTO(--(RESIDUO(B2:B4000;1)>0))3.964
Hora más temprana=TEXTO(MIN(RESIDUO(B2:B4000;1));"hh:mm")05:48
Hora más tardía=TEXTO(MAX(RESIDUO(B2:B4000;1));"hh:mm")21:17
Celdas con texto=CONTARA(B2:B4000)-CONTAR(B2:B4000)0

Las dos filas centrales son fórmulas matriciales. En Excel 2019 y anteriores hay que confirmarlas con Ctrl+Mayús+Entrar; en Microsoft 365 y Excel 2021 son fórmulas corrientes.

Escenario: Pon =SUMAPRODUCTO(--(RESIDUO(B2:B4000;1)>0)) en una celda encima de tu columna de fechas y déjalo ahí. Cualquier respuesta mayor que 0 significa que todo criterio de fecha de la hoja tiene que ser una valla y no una igualdad.

Pruébalo en la cuadrícula

04Por Qué un <= en la Fecha Final Tira el Último Día

FIN.MES(H2;0) devuelve el último día del mes a medianoche. Para marzo de 2026 eso es 46112 exacto, no 46112,999, así que "<="&FIN.MES(H2;0) significa "a medianoche del 31 o antes".

La medianoche del 31 es el instante en que empieza el día. Un envío escaneado a las 06:12 de esa mañana ya está fuera de la valla, y con él todos los que se entregaron después.

Kirkburn transportó 148 envíos el 31 de marzo de 2026, por 4.650,10 €, marcados entre las 06:12 y las 19:48. La refacturación no cogió ninguno.

Marzo de 2026Importe
Transportado en el mes112.045,60 €
Refacturado al cliente107.395,50 €
Perdido el día 314.650,10 €
Envíos perdidos148

La misma valla es correcta en una hoja tecleada a mano y errónea en un volcado. Por eso sobrevive a una revisión: quien la lee lleva años viéndola funcionar sobre fechas limpias.

05La Valla Que Funciona en Cualquier Columna

Acota un periodo con un >= en su primer instante y un < en el primer instante del periodo siguiente. El límite superior queda entonces más allá de cualquier hora que pueda llevar la última fecha, sin tener que nombrarla.

=SUMAR.SI.CONJUNTO(Neto;Marca;">="&H2;Marca;"<"&FIN.MES(H2;0)+1)

FIN.MES(H2;0)+1 es el primer día del mes siguiente a medianoche. Una marca del 31/03/2026 19:48 está por debajo; una del 01/04/2026 00:00 no. Nada se cuenta dos veces y nada se queda fuera.

La misma forma sirve para un día, una semana o un trimestre:

=SUMAR.SI.CONJUNTO(Neto;Marca;">="&H2;Marca;"<"&H2+1)
=SUMAR.SI.CONJUNTO(Neto;Marca;">="&H2;Marca;"<"&H2+7)
=SUMAR.SI.CONJUNTO(Neto;Marca;">="&H2;Marca;"<"&FECHA.MES(H2;3))

Una valla semiabierta acierta con los dos tipos de columna. Sobre fechas limpias sigue incluyendo el último día, porque entre esa medianoche y la siguiente no hay nada. Sobre marcas lo incluye entero.

Escenario: Coge tu SUMAR.SI.CONJUNTO de fin de mes, cambia "<="&FIN.MES(H2;0) por "<"&FIN.MES(H2;0)+1 y pon los dos totales uno al lado del otro. La diferencia es un día de actividad que nadie ha facturado nunca.

Pruébalo en la cuadrícula

06La Igualdad Exacta No Devuelve Nada

La versión ruidosa de este fallo es la igualdad. =SUMAR.SI.CONJUNTO(Neto;Marca;H2) con una fecha limpia en H2 devuelve 0,00 contra una columna con horas, porque ninguna marca es igual a medianoche.

Eso no lo publica nadie. Un informe diario lleno de ceros se detecta la primera mañana que se ejecuta y se corrige antes de comer, y quien lo corrige aprende algo.

La peligrosa es la valla. Devuelve una cifra del tamaño correcto, en la moneda correcta, un tres por ciento escasa, y lo hace todos los meses durante un año.

CriterioSobre una columna con horas¿Se detecta?
=SUMAR.SI.CONJUNTO(Neto;Marca;H2)0,00La primera mañana
">="&H2 con "<="&FIN.MES(H2;0)3% escasaNunca
">="&H2 con "<"&FIN.MES(H2;0)+1Correcta—

CONTAR.SI.CONJUNTO se comporta igual. =CONTAR.SI.CONJUNTO(Marca;H2) devuelve 0 sobre el volcado de marzo, y =CONTAR.SI.CONJUNTO(Marca;">="&H2;Marca;"<"&FIN.MES(H2;0)+1) devuelve 3.964.

Escenario: Pon =SUMAR.SI.CONJUNTO(Neto;Marca;H2) al lado de tu total acotado del mismo mes. Un cero te dice que la columna lleva horas, y te lo dice en una celda sin columnas auxiliares.

Pruébalo en la cuadrícula

07ENTERO Quita la Hora y REDONDEAR Mueve el Día

Cuando la hora sobra de verdad, ENTERO es la función que la quita. =ENTERO(B2) tira la parte decimal y deja la fecha, y una columna auxiliar con eso ya se puede comparar con una igualdad corriente.

=MULTIPLO.INFERIOR(B2;1) hace lo mismo, y =TRUNCAR(B2) también. Las tres dejan la marca en el día en que ocurrió.

REDONDEAR no. =REDONDEAR(B2;0) va al día entero más cercano, así que toda marca posterior a las 12:00 se va a la mañana siguiente: casi toda la tarde de Kirkburn, y los 148 envíos del 31 de marzo.

Sobre 31/03/2026 16:42Resultado
=ENTERO(B2)31/03/2026
=MULTIPLO.INFERIOR(B2;1)31/03/2026
=TRUNCAR(B2)31/03/2026
=REDONDEAR(B2;0)01/04/2026

Deja la columna auxiliar como fórmula en lugar de pegarla sobre sí misma como valores. El día que cambie la forma del volcado, una columna de fórmulas lo sigue y una pegada arrastra las fechas del mes pasado al informe de este.

Escenario: Pon =ENTERO(B2) y =REDONDEAR(B2;0) junto a una marca de las 16:42 y dales formato dd/mm/aaaa. Una es el día en que entregó la furgoneta; la otra es la mañana siguiente.

Pruébalo en la cuadrícula

08Arréglalo una Vez en Power Query

Una columna auxiliar trata el síntoma en una hoja. Si el volcado se actualiza en lugar de pegarse, el sitio donde quitar la hora es la consulta, y así todas las fórmulas de abajo ven fechas enteras.

En el editor, selecciona la columna y usa Transformar ▸ Fecha ▸ Solo fecha. El paso se graba una vez y se aplica en cada actualización posterior, lleguen las filas que lleguen.

Cambiar el tipo de la columna a Fecha hace el mismo trabajo. El icono del encabezado es la forma más rápida de saber cuál tienes: un calendario es una fecha, y un calendario con reloj es una fecha y hora.

Hazlo en el extremo correcto. Si la hora se usa en algún sitio — una ventana de entrega, un reloj de nivel de servicio, un recargo fuera de horario — conserva la marca y añade una columna de fecha al lado en vez de truncar encima.

Una marca grabada en UTC es otra pregunta. Truncarla fecha una entrega local de las 23:40 en el día anterior durante el invierno, y el arreglo es convertir a hora local primero y quitar la hora después.

Escenario: Abre tu volcado en Power Query, selecciona la columna de fechas y mira el icono de su encabezado. Si enseña un reloj, añade Solo fecha como paso y actualiza la hoja que depende de él.

Pruébalo en la cuadrícula

09Marcas Que Llegaron como Texto

Una columna de fechas puede fallar un criterio por un segundo motivo que desde fuera se ve igual. Si las marcas llegaron como texto — "31/03/2026 16:42" como cadena — no coincide nada, porque el texto nunca se compara contra un número.

=CONTARA(B2:B4000)-CONTAR(B2:B4000) es la cuenta que separa los dos casos. Devuelve 0 cuando la columna lleva fechas de verdad y el número de celdas de texto cuando no.

Las celdas de texto se alinean a la izquierda salvo que alguien las haya alineado a mano, y SUMAR.SI.CONJUNTO devuelve 0,00 para el mes en lugar de una cifra ligeramente escasa.

=VALFECHA(IZQUIERDA(B2;10)) convierte una de ellas, tomando la parte de fecha y dejando la hora. Para una columna entera, Datos ▸ Texto en columnas ▸ Finalizar la vuelve a leer con tu configuración regional, que es más rápido y da por hecho que tu equipo lee el día y el mes en el orden en que los escribió el fichero.

Escenario: Pon =CONTARA(B2:B4000)-CONTAR(B2:B4000) encima de tu columna de fechas. Cualquier cosa distinta de 0 significa que parte de las filas son texto, y esas filas son invisibles para todos los criterios de fecha de la hoja.

Pruébalo en la cuadrícula

10Dinámicas, Filtros y Búsquedas Fallan Igual

Una tabla dinámica sobre una columna de marcas sin agrupar da una fila por segundo distinto, así que marzo de 2026 presenta 3.964 fechas en vez de 31. Haz clic derecho en el campo, elige Agrupar y marca Meses: la agrupación trabaja sobre la parte de fecha y el informe sale bien.

El autofiltro es la razón por la que la gente concluye que los datos están bien. Su lista de casillas monta un árbol de Año ▸ Mes ▸ Día y archiva cada marca bajo su día, así que el 31 se ve perfectamente en el filtro mientras la fórmula de al lado dice otra cosa.

Una búsqueda falla igual que un SUMAR.SI.CONJUNTO por igualdad. =BUSCARX(H2;Marca;Neto) devuelve #N/D contra una columna con horas, y =BUSCARX(H2;ENTERO(Marca);Neto) encuentra la primera entrega del día.

Dónde apareceQué fallaEl arreglo
Filas de la dinámicaUna fila por marcaAgrupar ▸ Meses
BUSCARX sobre fecha#N/DComparar con ENTERO
Formato condicionalLa regla no salta=ENTERO($B2)=HOY()
Validación de datosRechaza toda fechaAcotar la regla

Escenario: Añade a tu volcado una regla de formato condicional =ENTERO($B2)=HOY() y comprueba que se encienden las entregas de hoy. La versión sin ENTERO es la que no ilumina nada en todo el día.

Pruébalo en la cuadrícula

11HOY, AHORA y la Propia Celda de Criterio

HOY() devuelve un día entero sin hora, y por eso es segura a los dos lados de una valla. =SUMAR.SI.CONJUNTO(Neto;Marca;">="&HOY();Marca;"<"&HOY()+1) es todo lo entregado hoy, a la hora que se escaneara.

AHORA() lleva la hora, así que ">="&AHORA() significa "desde este segundo" y devuelve las entregas de la tarde a las dos y casi nada a las cinco.

La celda de criterio merece la misma comprobación que los datos. Si H2 se pegó desde el volcado en vez de teclearse, puede llevar sus propias 08:00, y una valla construida sobre ella empieza ocho horas tarde.

Escenario: Pon =RESIDUO(H2;1) al lado de la celda de la que salen las fechas de tu informe. Si no es 0, envuelve la celda de criterio en ENTERO antes de que nada más la lea.

Pruébalo en la cuadrícula

12Ocho Trampas

  1. Dar formato de fecha a la columna. Cambia lo que ves y ni una sola comparación de las de debajo.
  2. "<=" sobre el último día. Se para a medianoche, y la medianoche es donde empieza el día, no donde acaba.
  3. REDONDEAR en lugar de ENTERO. Todo lo posterior al mediodía se va al día siguiente, y el total queda mal en los dos sentidos a la vez.
  4. Una celda de criterio con su propia hora. La valla es correcta y el límite llega ocho horas tarde.
  5. AHORA() donde se quería HOY(). El total de hoy encoge cada vez que la hoja recalcula.
  6. Pegar la columna auxiliar como valores. Deja de seguir al volcado la primera vez que el volcado cambia de forma.
  7. Fiarse del filtro. La lista de casillas agrupa las marcas por día; SUMAR.SI.CONJUNTO compara el número, y los dos no coinciden.
  8. Comprobar un solo mes. Agosto cuadró porque el 31 era domingo, y una muestra de uno no demostró nada de los otros once.

13Cinco Ejercicios

  1. Reprodúcelo. Mete veinte marcas de dos meses en una columna, suma un mes con "<="&FIN.MES(H2;0) y confirma que falta el último día.
  2. Cuenta las horas. =SUMAPRODUCTO(--(RESIDUO(B2:B21;1)>0)) debería declarar todas las filas con hora antes de que cambies nada.
  3. Acótalo. Cambia el límite superior por "<"&FIN.MES(H2;0)+1 y mira cómo el total se mueve exactamente un día de actividad.
  4. Rómpelo con REDONDEAR. Añade =REDONDEAR(B2;0) como columna auxiliar, suma sobre ella y busca la tarde en el mes siguiente.
  5. Arregla el origen. Carga las mismas filas con Power Query, añade Solo fecha y comprueba que el SUMAR.SI.CONJUNTO por igualdad ya devuelve el día.

Qué Llevarte

Nada de esta hoja estaba roto. Doce meses, doce cifras plausibles, una fórmula que se lee como la frase que diría cualquiera en voz alta, y una conciliación que pasó.

Lo que la hoja nunca enseñó fue la hora del día escondida detrás de cada fecha del volcado. Un formato de número la tapaba, el filtro la agrupaba y desaparecía, y solo la aritmética de debajo de los criterios llegó a verla.

Un criterio de fecha es seguro cuando es una valla: >= en el primer instante y < en el primer instante del periodo siguiente. Esa forma acierta con marcas y sigue acertando con fechas limpias, así que es la que hay que escribir por defecto.

Una celda dice si te afecta. =SUMAPRODUCTO(--(RESIDUO(B2:B4000;1)>0)) no cuesta nada dejarla en la hoja, frente a los 26.193,70 € que salieron de la ventana de noventa días mientras la hoja cuadraba.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy