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ño | Importe |
|---|---|
| Transportado en Kirkburn | 1.196.344,50 € |
| Refacturado al cliente | 1.159.139,90 € |
| Nunca facturado | 37.204,60 € |
| Meses cortos | 10 de 12 |
| Dentro de los 90 días | 11.010,90 € |
Alcance.
SUMAR.SI.CONJUNTO,CONTAR.SI.CONJUNTO,FIN.MES,RESIDUOyENTERO, 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.
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 preguntas | En una fecha limpia | En una marca de 16:42 |
|---|---|---|
| Lo que muestra la celda | 31/03/2026 | 31/03/2026 |
=B2=FECHA(2026;3;31) | VERDADERO | FALSO |
=RESIDUO(B2;1) | 0 | 0,695833 |
=TEXTO(B2;"hh:mm") | 00:00 | 16: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.
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ón | Fórmula | Kirkburn |
|---|---|---|
| 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.
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 2026 | Importe |
|---|---|
| Transportado en el mes | 112.045,60 € |
| Refacturado al cliente | 107.395,50 € |
| Perdido el día 31 | 4.650,10 € |
| Envíos perdidos | 148 |
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.
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.
| Criterio | Sobre una columna con horas | ¿Se detecta? |
|---|---|---|
=SUMAR.SI.CONJUNTO(Neto;Marca;H2) | 0,00 | La primera mañana |
">="&H2 con "<="&FIN.MES(H2;0) | 3% escasa | Nunca |
">="&H2 con "<"&FIN.MES(H2;0)+1 | Correcta | — |
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.
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:42 | Resultado |
|---|---|
=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.
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ícula09Marcas 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.
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 aparece | Qué falla | El arreglo |
|---|---|---|
| Filas de la dinámica | Una fila por marca | Agrupar ▸ Meses |
BUSCARX sobre fecha | #N/D | Comparar con ENTERO |
| Formato condicional | La regla no salta | =ENTERO($B2)=HOY() |
| Validación de datos | Rechaza toda fecha | Acotar 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.
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.
12Ocho Trampas
- Dar formato de fecha a la columna. Cambia lo que ves y ni una sola comparación de las de debajo.
"<="sobre el último día. Se para a medianoche, y la medianoche es donde empieza el día, no donde acaba.REDONDEARen lugar deENTERO. Todo lo posterior al mediodía se va al día siguiente, y el total queda mal en los dos sentidos a la vez.- Una celda de criterio con su propia hora. La valla es correcta y el límite llega ocho horas tarde.
AHORA()donde se queríaHOY(). El total de hoy encoge cada vez que la hoja recalcula.- Pegar la columna auxiliar como valores. Deja de seguir al volcado la primera vez que el volcado cambia de forma.
- Fiarse del filtro. La lista de casillas agrupa las marcas por día;
SUMAR.SI.CONJUNTOcompara el número, y los dos no coinciden. - 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
- 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. - Cuenta las horas.
=SUMAPRODUCTO(--(RESIDUO(B2:B21;1)>0))debería declarar todas las filas con hora antes de que cambies nada. - Acótalo. Cambia el límite superior por
"<"&FIN.MES(H2;0)+1y mira cómo el total se mueve exactamente un día de actividad. - Rómpelo con REDONDEAR. Añade
=REDONDEAR(B2;0)como columna auxiliar, suma sobre ella y busca la tarde en el mes siguiente. - Arregla el origen. Carga las mismas filas con Power Query, añade Solo fecha y comprueba que el
SUMAR.SI.CONJUNTOpor 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.