Fechas de vencimiento en un día determinado

En la edad de piedra de este blog, hace varios años atrás, tratamos el tema del cálculo de fecha de pago.
En esa nota mostramos dos casos:

# - la fecha de vencimiento sucede 30 días a partir de la fecha de la factura, cuando la intención es el mismo día un mes más tarde. En este caso, suponiendo que la fecha de la factura está en la celda B3, =B3+30 sino =FECHA.MES(B3,1)

Si usáramos =B3+30 el resultado sería 24/11/2014

# - la fecha de vencimiento debe caer en un día determinado del mes, por ejemplo el 15. Para este caso usamos la fórmula 

=SI(DIA(B3)<=15,FIN.MES(B3,0)+15,FIN.MES(B3,1)+15)

(en la nota original usé una fórmula innecesariamente más complicada)

Un lector me consulta cómo calcular la fecha de pago de manera que caiga en un día determinado de la semana. Por ejemplo, una empresa que decide pagar 30 días después de la fecha de la factura pero sólo los días lunes. Consideremos esta tabla

La columna C muestra la fecha de vencimiento 30 días a partir de la fecha de la factura (el mismo día, un mes más tarde). En la celda C3 vemos que el vencimiento cae el 2/11/2014 que es un domingo; para corregir la fecha al lunes más cercano en la celda D3 ponemos la fórmula

=FECHA.MES(B3,1)+(2-DIASEM(FECHA.MES(B3,1)))

Al copiar la fórmula al resto de las celdas vemos que los vencimiento ocurren siempre los lunes (3/11/14, 10/11/14, etc.)

Esta fórmula funciona así:

=FECHA.MES(B3,1) da la fecha que aparece en la columna C (30 días de la fecha de la factura);

(2-DIASEM(FECHA.MES(B3,1))) es el "factor de correción" donde DIASEM(FECHA.MES(B3,1)) da el número de orden del día del vencimiento antes de la correción (el que aparece en la columna C), en nuestro caso 1 (domingo) y el resultado de la expresión será 2-1=1.

Algunos de mis agudos lectores ya habrán descubierto un problema potencial en este cálculo. Si observamos la factura en la fila 9 de la tabla, vemos que los 30 días caen el 8/11/2014 pero al corregir la fecha con la fórmula el pago ocurrirá 5 días antes, el 3/11/2014.

Si queremos que el pago ocurra siempre el mismo día o después de los 30 días debemos modificar nuestra fórmula a 

=FECHA.MES(B3,1)+SI(DIASEM(FECHA.MES(B3,1))<=2,(2-DIASEM(FECHA.MES(B3,1))),7+(2-DIASEM(FECHA.MES(B3,1))))

Una aclaración importante: la función DIASEM usa el argumento Tipo para determinar cuál es el primer día de la semana.

En mi ejemplo el argumento Tipo está en blanco significando que el día 1 es el domingo. Si queremos el número de orden 1 sea el lunes, el valor de Tipo debe ser 2.

Calcular días por períodos

Supongamos que queremos calcular el interés a cobrar por una deuda (por ejemplo, un pago atrasado). Durante el lapso transcurrido hay períodos con distintas tasas de interés.
El problema consiste en calcular cuantos días del lapso de la deuda caen en cada período de interés.
Consideremos esta ejemplo (el cuaderno es interactivo y puede descargarse)





El lapso de la deuda corre del 15/02/2014 al 27/06/2014, 133 días. Durante este lapso hay tres períodos de interes, tal como aparece en el rango B6:E8.

Nuestra tarea es asignar los 133 días a los distintos períodos de interés, tal como aparece en el rango F6:F8. La celda F6 contiene la fórmula

=SUMAPRODUCTO((((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))>=$C$3)*(((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3))

En esta fórmula usamos la técnica que ya mostré en la nota "Calcular días por años entre dos fechas". El funcionamiento es el siguiente:

• la expresión (C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1)) al estar dentro de la función SUMAPRODUCTO crea un vector de fechas , en nuestro caso {01/01/2014,02/01/2014,03/01/2014...,31/03/2014}

• al comparar los miembros de este vector con la expresión >=$C$3 creamos un vector con los valores VERDADERO o FALSO (según se cumpla la condición o no)

• la segunda expresión ((C6-1)+FILA(INDIRECTO("1:"&(D6-C6+1))))<=$D$3) funciona de la misma manera para la fecha del fin del lapso

• SUMAPRODUCTO multipllica ambos vectores resultando 1 cuando se multiplican dos valores VERDADERO y 0 para los restantes casos. La suma interna del vector da como resultado el número de días comprendido dentro del período de interés.

Comentarios en tablas dinámicas.

Con un pie en el avión rumbo a Italia (viaje de trabajo y placer, lamentablemente no en las proporciones deseadas) recibo un llamado de un amigo consultándome sobre comentarios en tablas dinámicas.

- ¿Cómo hago para poner poner un comentario en una tabla dinámica?
- ¿Dónde en la tabla dinámica?
- ¿Cómo dónde en la tabla? ¡En una celda de la tabla! ¿Dónde va a ser      sino?
- Bien, sucede que las tablas dinámicas no tienen celdas, eeeehh, tienen   pero no exactamente, es decir, eeehh, bueno...
- ¿Te podés explicar por favor?!!

Me explico. Por lo general usamos el menú contextual de para poner un comentario en una celda

Pero si intentamos hacerlo en una celda que es parte de una tabla dinámica, la opción "Insertar comentario" no aparece en el menú contextual

Esto no es casual ni tampoco un error. El contenido de las celdas que ocupa una tabla dinámica cambian (obviamente en forma dinámica) con los cambios en los datos de la base de datos que la alimenta o cuando cambiamos el enfoque de la tabla. Pero los comentarios queda ligados a la celda y no dependen del valor que le asigne a esa celda la tabla dinámica.

A pesar de esto podemos introducir un comentario en la celda usando el menú de la cinta Revisar-Comentarios-Nuevo Comentario

Supongamos que hemos puesto un comentario en la celda C8 del ejemplo explicando por qué las ventas de Brasil han crecido en un 50%

Sin embargo cuando apuntamos a la celda no podremos ver el comentario; en su lugar aparecerá la información contextual relacionada al valor de la tabla que ocupa la celda

Para que aparezca el comentario tenemos que modificar la propiedad "Mostrar información contextual..." en el menú de "Opciones de tabla dinámica-Mostrar"

Lamentablemente aquí no terminan nuestros penares. 

Ahora quitemos a Brasil del informe usando el filtro del campo

El resultado será que ahora Canadá ocupa la celda C8, pero el comentario sobre Brasil sigue apareciendo en la celda

Conclusión: no usar comentarios en celdas ocupadas por valores de una tabla dinámica; y si queremos usarlos, convertir la tabla dinámica en una tabla estática (copiar-pegar valores).