Vencimientos en día hábil posterior

En la nota anterior vimos como en el caso que una fecha de vencimiento caiga en día no laboral (feriado o fin de semana) calcular el día hábil inmediato anterior. Por suspuesto no tanscurrió mucho tiempo hasta que un lector me consultó cómo hacer el cálculo al día hábil inmediato posterior. Por ejemplo, si la fecha de vencimiento cae un domingo, calcular la fecha del lunes siguiente.
Ambos casos serían muy sencillos de calcular si las funciones DIA.LAB o  DIA.LAB.INT contaran con la opción "0" (los siete días de la semana son laborales).
Como esa opción no existe, combinamos las funciones MAX, FILA y DIASEM en esta fórmula matricial

=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))

donde C4 contiene la cantidad de días corridos al vencimiento y C5 la fecha inicial

El funcionamiento de la fórmula está explicado en la primer nota de la serie. Básicamente, creamos un vector de fechas partiendo de la fecha de vencimiento hacia atrás, donde las fechas de los fines de semana o de feriados reciben el valor 0; luego MAX extrae el valor mayor, que es el primer día hábil "hacia atrás".

Volviendo a la consulta de nuestro lector, queremos encontrar el primer día hábil posterior al vencimiento si este cae en día no laboral. Para encontrar la fecha vamos a aplicar una lógica similar: creamos un vector de fechas "hacia adelante" (los días posteriores a la fecha de vencimiento antes de las correcciones); las fechas que caen en días no laborales (fin de semana o feriado) reciben el valor cero. Ahora tenemos que encontrar la fecha hábil más cercana, es decir la menor, lo que podemos hacer con la función MIN (recordemos que en Excel las fechas son miembros de un serie de números enteros). Pero aquí nos encontramos con el problema que el vector de fechas incluye ceros.

Para encontrar el menor de los valores positivos de una serie (en nuestro caso, la primer fecha hábil posterior al vencimiento) excluyendo los ceros usaremos la función K.ESIMO.MENOR combinada con SUMAPRODUCTO en esta fórmula matricial

=K.ESIMO.MENOR(($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6),SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1)

Para ahorrar a mis estimados lectores los mareos (y talvez ligeras náuseas) que pueda causar la lectura de semejante fórmula, vamos a explicarla. Podemos dividir esta fórmula en dos partes

La expresión

$C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6

crea un vector de fechas donde el primer miembro es la fecha de vencimiento (fecha inicial + más diás corridos al vencimiento) y los restantes son los números de serie de las fechas siguientes o cero si la fecha cae en día no laboral. "Feriados" es un nombre definido que se refiere al rango que contiene las fechas de los feriados.
Para facilitar la lectura podemos definir un nombre que se refiere a esta fórmula (vector_fechas)

La expresión

SUMAPRODUCTO(--((($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)))*(ESERROR(COINCIDIR($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),Feriados,0)))*(DIASEM($C$5+$C$4-1+FILA(INDIRECTO("1:"&$C$4)),2)<6))=0))+1

calcula cuantos ceros hay en el vector creado por la expresión anterior que hemos encapsulado en el nombre "vector_fechas".

Si usamos el nombre "vector_fechas" en nuestra fórmula, obtenemos una versión mucho más fácil de leer y entender:

=K.ESIMO.MENOR(vector_fechas,SUMAPRODUCTO(--(vector_fechas=0))+1)

Otra ventaja de esta sintaxis es que no hay que ingresar la fórmula en forma matricial.

Veamos este ejemplo

Le fecha de vencimiento, antes de las correcciones necesarias, cae el 15/03/2015 que es un domingo; en la tabla de feriados vemos que el lunes y martes siguientes son feriados, de maner que el resultado de la fórmula es el miércoles 18/03/2015.

También podemos usar esta variante:

=K.ESIMO.MENOR(vector_fechas,INDICE(FRECUENCIA(vector_fechas,0),1)+1)

donde el número de ceros en el vector es calculado con la función FRECUENCIA. También en este caso no hace falta ingresar la fórmula en forma matricial.

Una tercera variante es usar la función MIN combinada con SI, en esta fórmula matricial

=MIN(SI(vector_fechas=0,"",vector_fechas))

El cuaderno con el ejemplo puede descargarse aquí.

Vencimientos en día hábil - versión mejorada

En la nota anterior mostré como calcular fechas de vencimientos que siempre caigan en días hábiles, no en fines de semana o feriados. En caso de caer en día no hábil o feriado, la fecha calculada debía ser el primer día hábil anterior a la fecha original. (Para calcular el día hábil posterior, ver esta nota)

Para hacerlo propuse esta fórmula usando las funciones DIASEM, MAX, ESERROR y COINCIDIR junto con una constante vectorial. La fórmula en la celda C7 es

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6))

Esta fórmula tiene algunas debilidades.
La notación de las constantes vectoriales cambia de acuerdo a las definiciones regionales. Para ciertas configuraciones, el vector debe anotarse de esta manera:

=MAX((C5+C4-{7\6\5\4\3\2\1\0})*(ESERROR(COINCIDIR(C5+C4-{7\6\5\4\3\2\1\0};Feriados;0)))*(DIASEM(C5+C4-{7\6\5\4\3\2\1\0};2)<6))

usando \ en lugar de ; lo cual puede confundir a alguno de mis lectores. Para evitar este problema podemos usar esta sintaxis:

=MAX((C5+C4+1-FILA(1:7))*(ESERROR(COINCIDIR(C5+C4+1-FILA(1:7);Feriados;0)))*(DIASEM(C5+C4+1-FILA(1:7);2)<6))

donde creamos el vector usando la función FILA e ingresando la fórmula en forma matricial (apretando simultáneamente las teclas Ctrl - Mayúsculas - Enter).

Otro problema más serio es que la constante vectorial es exactamente eso, una constante. Arbritariamente uso un vector de 8 miembros para generar un vector de ocho fechas, de la fecha de vencimiento hacia atrás, suponiendo que no hay feriados que duren más de una semana. Pero esta suposición no tiene que ser necesariamente cierta en todos los casos. Lo ideal sería que ese vector de fechas fuera dinámico. Para hacerlo podemos basarnos en la fórmula matricial anterior usando la función INDIRECTO para generar un rango dinámico de fechas. La fórmula es:

=MAX((C5+C4+1-FILA(INDIRECTO("1:"&C4)))*(ESERROR(COINCIDIR(C5+C4+1-FILA(INDIRECTO("1:"&C4));Feriados;0)))*(DIASEM(C5+C4+1-FILA(INDIRECTO("1:"&C4));2)<6))

INDIRECTO("1:"&C4) crea un vector de tantos valores como días transcurren de la fecha incial a la fecha de vencimiento (en nuestro ejemplo, 30).

Calcular fecha de vencimiento en día hábil

Como muchas otras, también esta nota se originó en la consulta de un lector. Éste necesitaba computar fechas de vencimiento pero de manera tal que si la fecha de vencimiento caía en un feriado, la fecha fuera corregida al día hábil anterior (para el día posterior, ver esta nota). En sus propias palabras:

supongamos que la fecha inicial es 1 de abril del 2015, y en 30 días la persona tiene que pagar, esto quiere decir el 1 de mayo de 2015, PERO la fecha de pago no puede caer ni en dias festivos ni en fines de semana. se que existe la formula DIA.LAB.INTL pero no me sirve porque me omite todos los fines de semana o todos los domingos, para esto la fecha de pago tiene que ir antes (en caso de ser festivo o fin de semana); supongamos, si cae en domingo la fecha pasa para el viernes de esa misma semana, no para el lunes

Como se trata de días corridos, el proceso de cálculo tiene que ser el siguiente:

1. partimos de la fecha inicial y le sumamos 30;
2. evaluamos el resultado para saber si cae en un fin de semana o día festivo (para los días festivos necesitaremos un rango que los contenga);
3. si el resultado cae en día hábil, el cálculo queda concluido;
4. si el resultado cae en feriado, calculamos cuál es el día hábil anterior más cercano.

Este último punto es el más problemático ya que puede darse la situación de dos o más días festivos corridos.

Consideremos este ejemplo:

• En el rango F4:F8 tenemos la lista de feriados (arbitrarios, a los únicos efectos del ejemplo); 
• en la celda C4 el período en días corridos al vencimiento; 
• en la celda C5 la fecha inicial; 
• en la celda C6 el cálculo de la fecha de vencimiento sin correcciones que calculamos con la trivial fórmula =C5+C4. 
• Para mayor comodidad he definido un nombre que contiene los días de la semana lo que permite calcular el día de semana de cada fecha, como en la celda D5 con esta fórmula =INDICE(rngSemana,DIASEM(C5,2)).

Como podemos ver el vencimiento cae un sábado, por lo cual debemos corregir al viernes anterior, es decir, un día atrás. Si este fuera siempre el caso nuestra fórmula sería relativamente sencilla: si el día de la semana es sábado, restar un día del resultado; si es domingo, restar dos.

El cálculo se complica si el resultado coincide con un feriado. En este caso debemos encontrar la primer fecha anterior que no es ni feriado ni fin de semana.

Consideremos ahora este caso:

La fecha inicial es el 13/05/2015 y por lo tanto la fecha de vencimiento tendría que ser el 12/06/2015 que es viernes. Pero como podemos ver en la lista de feriados, el viernes 12/06/2015 y también el jueves 11/06/2015 son feriados, por lo que el resultado de nuestro cálculo deberá ser el miércoles 10/06/2015.

La fórmula en la celda C7, donde obtenemos el resultado correcto, es ésta:

=MAX((C5+C4-{7;6;5;4;3;2;1;0})*(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0)))*(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6))

Vamos a explicar esta fórmula paso por paso.

C5+C4 - La fecha inicial más 30 días;

{7;6;5;4;3;2;1;0} - un vector vertical cuyos miembros son números enteros del 0 al 7. Dependiendo de las definiciones del sistema, en lugar del punto y coma (;)  tendremos que usar \ (por ejemplo: {7\6\5\4\3\2\1\0}). También podemos crear el vector vertical usando comas y la función TRANSPONER, por ejemplo: TRANSPONER({7,6,5,4,3,2,1,0}).

C5+C4-{7;6;5;4;3;2;1;0} - esta expresión crea un vector de fechas, en nuestro caso del 05/06/15 al 12/06/15. Podemos visualizarlo usando la auditoría de fórmulas, donde las fechas aparecen con el correspondiente número de serie

(ESERROR(COINCIDIR(C5+C4-{7;6;5;4;3;2;1;0},Feriados,0))) - crea un vector del mismo tamaño con valores VERDADERO si la fecha no se encuentra en la tabla de los feriados (COINCIDIR dá en ese caso el error #N/A) y FALSO si se trata de un feriado.

(DIASEM(C5+C4-{7;6;5;4;3;2;1;0},2)<6) - crea un vector de valores VERDADERO si la fecha cae entre lunes y viernes (incluidos) y FALSO si cae en el fin de semana.

Al multplicar los últimos tres vectores entre si creamos un vector de valor 0 (cuando alguno de los valores es FALSO) o el número de serie de la fecha (cuando los tres valores correspondientes son VERDADERO). Podemos visualizar estas operaciones en esta tabla que muestra en cada fila un elemento de cada vector

Finalmente, la función MAX extrae el valor máximo del producto de los tres vectores (los valores que aparecen en la columna H en la imagen), que es la fecha del primer día hábil anterior a la fecha de vencimiento sin correcciones.

El archivo con el ejemplo puede descargarse aquí

Ver post con una versión mejorada.

Calculando el día del año con Excel

Dada una fecha Excel cuenta con funciones para calcular el día del mes, la semana del año, el mes o el año de la fecha. Por ejemplo si queremos saber en qué semana del año cae la fecha 25 de marzo del 2015, usamos la función NUM.DE.SEMANA

Excel no tiene una función para calcular el día del año de una determinada fecha (un valor entre 1 y 365 o 366, si el año es bisiesto). Por ejemplo le 25/03/2015 es el día número 84 del año.
Para calcular el número de día del año de una fecha podemos usar esta fórmula:

=B3-FECHA(AÑO(B3),1,0)

Para calcular cuántos días faltan hasta el fin del año, modificamos la fórmula de esta manera:

=FECHA(AÑO(B3),12,31)-B3

Cálculo de tiempos y formato personalizado en Excel

Ayer apareció por mi oficina Armando, del departamento de planificación. Me llamó la atención que llegara solo porque, por lo general, se lo ve con Raquel, la del departamento de producción (las malas lenguas dicen que...).

- Como seguramente sabés, los datos de uso de las máquinas están en minutos.
- Si, lo se.
- ¿Cómo hago para mostrarlos en días, horas y minutos?
- Poniendo el formato correspondiente. Digamos "dd hh:mm"
- No sabía que existía ese formato, no aparece en la lista.
- Es un formato personalizado.
- Ah!, gracias.

Como era de esperar, Armando apareció de nuevo a los pocos minutos.

- Mirá, tu método no funciona; en lugar de 1 día, 22 horas y 40 minutos, me da este resultado

- ¿Te acordás lo que te explique sobre cómo Excel calcula fechas y horas? Tenes que dividir los 2800 minutos por 1440.

- Pero, ¿por qué 1440?
- Porque tu dato está en  minutos, y en un día hay 1440 minutos (60 x 24). Como explicaba en esa nota, que parece ser no leiste, los días en Excel están representados por una serie de números enteros y las horas, minutos y segundos por la parte decimal. Así 1 es un día, una hora es 1/24 (0.466667), un minuto es 1/1440 y un segundo es 1/86400 (24 x 60 x 60). Fijate en esta tabla

- Entendido, gracias. Pero cómo hago para que se entienda que se trata de días/horas/minutos. Si te fijas en la celda D3 de tu tabla parece ser 1 hora, no un día.
- Agregando texto en el formato personalizado; por ejemplo dd "dias" hh "horas y" mm "minutos". Fijate en esta tabla

- ¿Y si quiero que cuando sea un día aparezca "01 día" y no en plural?
- Usando formato condicional con esta regla

El formato condicional se suma al formato de la celda. Si queremos aplicar lo mismo para las horas y los minutos tenemos que crear un formato condicional para cada caso.

Tip para escribir macros eficientes en Excel

Hay muchas normas de buenas prácticas y tips para escribir macros eficientes. Una posibilidad raramente mencionada es usar en nuestros códigos métodos incorporados de Excel que podemos grabar con la grabadora de macros.

Un ejemplo puede ser la macro que propuse para importar fechas de un archivo .csv. La macro usa el loop For Each - Next para convertir fechas en formato mes/día/año (como en los Estados Unidos) al formato día/mes/año en uso en la mayoría de los países de habla hispana.

En lugar del código podemos usar la grabadora de macros para usar el método Texto en Columnas (Datos-Texto en Columnas) en nuestro código en lugar del loop For Each - Next. La ventaja inmediata de la grabadora de macros es que nos exime de tener que conocer la sintaxis del método. Además, como veremos más adelante, esta funcionalidad incorporada de Excel es mucho más eficiente.

No nos limitaremos a grabar la macro sino que eliminaremos las partes innecesarias; luego agregaremos variables para que nuestro código sea lo más flexible posible (al contrario del código que resulta de la grabadora de macros).

Supongamos que hemos importado un archivo .csv que contiene 400000 registros de fechas. Como el archivo fue originado en los Estados Unidos, tenemos que cambiar el formato de los datos, tal como explicamos en la nota mencionada.

Después de importar el archivo, activamos la grabadora de macros y usamos Texto en Columnas para transformar las fechas

El código resultante es el siguiente

Eliminamos la primer línea del código, suponiendo que el usuario activará la macro después de haber elegido el rango a convertir. También podemos eliminar las propiedades definidas por defecto, es decir, aquellas que no hemos cambiado (como norma, aquellas donde el valor de la propiedad es False)

Podemos dar un paso más adelante y permitir al usuario definir donde pegar el resultado (en el código de arriba Destination:=Range("A1")); además queremos verificar que el usuario haya elegido un rango que contengo por lo menos dos celdas

Ahora podemos verificar cuál es el código que corre más rápido: el que usa el loop For each - Next, de la nota mencionada, o éste basado en el método incorporado de Excel.

En mi máquina (Dell Latitude E5540 con procesador Intel Core i5-4300, 8 GB RAM, Excel 2010 64-bit), la macro que usa Texto en Columnas tomó 4.3 segundos en convertir las 400 mil fechas. La misma tarea con el loop For Each - Next tomó 180 segundos.

Conclusión: siempre considerar usar los métodos incorporados en Excel en nuestras macros.

Fechas de .csv a Excel - otras técnicas

En relación a la nota sobre los problemas que pueden surgir al importar fechas de un archivo texto .csv a Excel, dos lectores me sugieren técnicas que no fueron tratadas en el post. Javi sugiere usar Texto en Columnas (la opción más obvia que ignoré olímpicamente, mea culpa!) y Miguel (Power User en Español) sugiere usar la propiedad "locale" del Power Query.

Ambas técnicas merecen algo más que un comentario al pie del post, así que vamos a mostrar cómo usarlas.

Recordemos que el formato de fechas en los distintos países genera en potencia un problema al importar fechas de un archivo .csv a Excel. Al abrir directamente un archivo .csv, Excel "decide" que tipo de dato irá en cada celda. Todo lo que se vea como número será transformado en número (también si va precedido por uno o más ceros); todo lo que se vea como fecha será transformado en fecha. Todo lo demás será importado como texto.
Dado que el formato de fecha en los Estados Unidos (y en otras áreas del mundo) es mes/día/año mientras que la mayoría de los países hispanoparlantes y en Europa es día/mes/año, las fechas pueden ser importadas incorrectamente. Por ejemplo la fecha 10/06/2014 en los Estados Unidos es el 6 de octubre, mientras que en la Argentina es el diez de junio.

La técnica sugerida por Javi es usar Texto en Columnas y en el tercer paso elegir el formato MDA lo que transformará las fechas en forma correcta. Este video muestra la técnica



La técnica sugerida por Miguel es más avanzada y requiere que tengamos instalado el complemento Power Query (Excel 2010 en adelante).

Entre otras fuentes el Power Query permite importar datos también de archivos .csv. Una vez importados los datos al editor del Power Query podemos determinar el formato de fecha del archivo de origen (la propiedad "locale") para que estas sean interpretadas correctamente. Al cargar los datos a la hoja de Excel estos serán transformados correctamente.

Los datos en el archivo .csv son los siguientes

El proceso con el Power Query puede verse en este video

Importar fechas de un archivo .csv a una hoja de Excel

Ya hemos tocado en el pasado los problemas que pueden surgir cuando abrimos en forma directa archivos texto .csv en hojas de Excel. Por "forma directa" me refiero a archivos .csv abiertos con un doble click o usando Abrir. Excel interpreta los datos de acuerdos a ciertas reglas y pueden producir cambios indeseados. Por ejemplo el texto "012345" que representa, digamos, un número de catálogo será transformado en "12345".

El problema es particularmente grave cuando los datos importados son fechas. Supongamos que recibimos un archivo .csv con una lista de fechas que nos envía una empresa de los Estados Unidos. En los Estados Unidos se usa el formato mes/día/año mientras que en la mayoría de los países hispanoparlantes el formato de fecha es día/mes/año. Al abrir el archivo .csv directamente, los valores que Excel no interpreta como fechas de acuerdo a las definiciones regionales serán transformados en texto.Veamos este ejemplo:

Podemos ver que algunas fechas están alineadas a la izquierda y otras a la derecha.  Los valores alineados a la derecha han sido importados como fechas (al ser números Excel los alinea a la derecha), mientras que los valores alineados a la izquierda son texto. Esto se debe que Excel no puede interpretar  esos valores como fecha siguiendo el formato regional día/mes/año (por ejemplo en la celda A2, donde el número de mes sería 23).

El valor en la celda A3 nos muestra el problema más grave que se puede generar cuando importamos archivos .csv con fechas. La fecha, siguiendo el formato de los Estados Unidos, es el 6 de Octubre pero Excel la ha transformado en el 10 de Junio.

Señalemos que los datos importados deben ser fechas, de manera que podamos realizar operaciones con ellos.

Podemos transformar los textos en fechas usando una fórmula como

=SI(ESTEXTO(A3),VALOR(EXTRAE(A3,4,2)&"/"&IZQUIERDA(A3,2)&"/"&DERECHA(A3,4)),VALOR(TEXTO(A3,"mm/dd/yyyy")))

Podemos ver que el valor de la celda A3 es transformado correctamente por la fórmula en 06/10/2012.

Otra solución es usar una macro para forzar la transformación. La ventaja de la macro consiste en que no debemos crear las fórmulas para cada hoja; podemos guardar la macro en el cuaderno Personal y usarla en cada hoja que necesitemos sin necesidad de cargarla con fórmulas.

El código básico de la macro es

Sub USDdate_to_EURdate()
    Dim rngcell As Range

    On Error Resume Next
    For Each rngcell In Selection
        rngcell = CDate(Format(rngcell, "mm/dd/yyyy"))
    Next rngcell
    On Error GoTo 0

End Sub

Esta macro reemplaza los valores en el rango seleccionado por fechas con formato (dd/mm/yyyy).

Este código más elaborado nos permite elegir el rango donde copiar los resultados

Sub USDdate_to_EURdate_2()
    Dim rngcell As Range
    Dim rngOrigin As Range, rngDest As Range
    Dim iX As Long

    'seleccionar el rango a transformar
    Set rngOrigin = Application.InputBox(prompt:="Seleccione el rango a transformar", _
                                        Title:="Rango a transformar", _
                                        Type:=8)

    'comprobar si el rango elegido es vertical/columna
    If rngOrigin.Columns.Count > 1 Then
        MsgBox "El rango seleccionado debe contener solo una columna", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    'seleccionar la primer celda del destino
    Set rngDest = Application.InputBox(prompt:="Seleccione la primer celda del rango del destino", _
                                        Title:="Destino", _
                                        Type:=8)
    'comprobar que se haya elegido una sola celda
    If rngDest.Count > 1 Then
        MsgBox "Debe seleccionar solo una celda", vbCritical, "Error en la seleccion"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    On Error Resume Next 'en caso de haber celdas vacias o valores no validos en el rango elegido
    For iX = 0 To rngOrigin.Count - 1
        rngDest.Offset(iX, 0) = CDate(Format(rngOrigin(iX + 1), "mm/dd/yyyy"))
    Next iX
    On Error GoTo 0
    Application.ScreenUpdating = True
 
End Sub

Cálculo de duración de procesos por turnos

Los cálculos con fechas y horas en Excel suelen ser un tanto truculentos. Veamos por ejemplo esta consulta de una de mis lectoras:

Suponiendo que es 14-nov-14 8:00 am, tengo un proceso que dura 9 horas y mi turno dura 8 horas, cómo puedo hacer para que el día 15-nov-14 la hora de finalización sea 9:00am

Es decir, la tarea se realiza en un turno que empieza a las 08:00 AM y dura ocho horas. Si la tarea se extiende por más de ocho horas, ésta será completada el día siguiente.

La fórmula que nos permite hacer este cálculo es la siguiente

=SI(C5>C2,B5+TRUNCAR(C5/C2)+RESIDUO(C5,C2),B5+C5)

Veamos como funciona esta fórmula:

C5>C2 determina si el proceso puede completarse en las horas del turno;

B5 contiene la fecha y hora del comienzo del proceso;

TRUNCAR(C5/C2) nos calcula cuantos días debemos agregar a la fecha de iniciación del proceso para completarlo (múltiplos de la duración del turno);

RESIDUO(C5,C2) calcula cuántas horas debemos agregar a los múltiplos de ocho horas (la duración del turno).

De esta manera, si el proceso dura más de ocho horas, la función SI aplica la primer parte de la fórmula:

B5+TRUNCAR(C5/C2)+RESIDUO(C5,C2)

Las cosas se complican un poco (más) si queremos calcular la fecha de finalización del proceso tomando en cuenta sólo los días laborables.
Contamos para ello con la función DIA.LAB, es cierto, pero hay un pequeño problema. DIA.LAB no toma en cuenta las horas, sólo las fechas, como podemos ver en este ejemplo:

Dado que el 14/11/2014 cae un viernes, el próximo día laboral es el 17/11/2014 (lunes), pero podemos ver que la fórmula no toma en cuenta la hora de comienzo, sólo la fecha.
A pesar de esto podemos usar DIA.LAB para nuestro cálculo, de esta manera:

=SI(C5>C2,DIA.LAB(B5,TRUNCAR(C5/C2))+C1+RESIDUO(C5,C2),B5+C5)

donde C1 es la hora de comienzo del turno.

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.

La función MONEDA de Excel

¿Alguien usó alguna vez la función MONEDA de Excel? La función MONEDA y sus dos "primas hermanas" MONEDA.FRAC y MONEDA.DEC pertenecen esa colección de funciones que ni siquiera sabíamos que existen.

Según la ayuda en línea de Excel el objetivo de la función MONEDA es convertir un número en texto usando formato de moneda

Introducimos un número (o referencia a una celda que contiene un número), definimos la cantidad de decimales y el resultado es el número precedido por el símbolo de la moneda (de acuerdo a las definiciones del sistema). Lejos de ser la función más sofisticada de Excel.

Las mencionadas "primas" tienen usos que pueden ser útiles en ciertas circunstancias. EL objetivo original de la función MONEDA.FRAC, siempre de acuerdo a la ayuda en línea de Excel, es:

Convierte una cotización de un valor bursátil, expresada en forma decimal, en fraccionaria. Use MONEDA.FRAC para convertir números decimales de moneda, como precios de valores bursátiles, en fracción.

En lugar de intentar explicar el significado de la definición, voy a mostrar un uso posible.

Por lo general los sistema de control de horarios usan la notación decimal. Por ejemplo, 2.5 horas representa 2 horas y 30 minutos; 2.75 representa 2 horas y 45 minutos. La forma decimal puede conducir a confusiones y ya hemos visto cómo convertir esta forma en formato horario:

1. dividimos 2.75 por 24
2. cambiamos el formato de la celda a [hh]:mm
3. el resultado será 02:45

En lugar del formato horario podemos usar MONEDA.FRAC de la siguiente manera

=MONEDA.FRAC(A2,60)

La parte decimal del número muestra ahora los minutos (2 horas, 45 minutos).

La función SIFECHA (DATEDIF) con varios períodos

Ya hemos hablado en el pasado sobre la función SIFECHA (DATEDIF en inglés) y sus bondades. A pesar de su utilidad esta función sólo está documentada en la versión 2000 de Excel. Por algún motivo Microsoft decidió "pasarla a la clandestinidad", aunque está disponible en todas las versiones posteriores de Excel.
Un lector me consultaba sobre cómo usarla para calcular la antigüedad de un empleado que se ha reincorporado a la empresa en varias oportunidades.

Veamos este ejemplo

Las columnas B y C muestran la fecha de comienzo y final de cada período; las columnas D, E y F contienen estas fórmulas:

• =SIFECHA(B2,C2,"y"), para calcular los años
• =SIFECHA(B2,C2,"ym"), para calcular el resto de los meses
• =SIFECHA(B2,C2,"md"), par calcular el resto de los días.

Nótese que el argumento "y" indica que se calculan los años, pero dependiendo de las definiciones regionales del sistema, debe usarse "a" en su lugar.

La tabla muestra que nuestro empleado ha trabajado al presente 10 años, 21 meses y 55 días. Por suspuesto, la forma de presentar este cálculo debe ser 11 años, 10 meses y 25 días. Podemos corregir la fila 5 manualmente ("pasamos" 12 meses a los años quedando 11 años y 9 meses; pasamos 30 días a los meses quedando 10 meses y 25 días), pero más práctico es usar SIFECHA de la siguiente manera:

Las fórmulas con SIFECHA aquí son:

• =SIFECHA(B2+B3+B4,C2+C3+C4,"y")
• =SIFECHA(B2+B3+B4,C2+C3+C4,"ym")
• =SIFECHA(B2+B3+B4,C2+C3+C4,"md")

Dado que Excel maneja las fechas como números enteros de una serie, podemos sumar las fechas de comienzo como "fecha inicial" y de la misma manera sumar las fechas de finalización como argumentos de la función.

Si necesitamos expresar el resultado en un único número, a los efectos de realizar algún cálculo, podemos usar esta fórmula que dará una buena aproximación:

La parte decimal del resultado expresa los meses y días por encima de los 11 años. Podemos interpretar este número de la siguiente manera:

donde las fórmulas utilizadas son

Calcular días por años entre dos fechas

Supongamos una lista de fechas de inicio y finalización de tareas (o contratos o cualquier otra actividad) que se extienden a más de una año, como en este ejemplo:

Podemos ver, por ejemplo, que la Tarea 1 empieza en el 2013 y termina en el 2015. ¿Cómo hacemos para calcular cuántos días caen en cada año?

Para resolver este problema vamos a usar una técnica no tan conocida: crear dentro de la fórmula un vector implícito (o "virtual"). El concepto quedará más claro cuando expliquemos la fórmula.

La columna "Total de días" muestra el total entre ambas fechas (incluida el día del comienzo) usando la fórmula =SIFECHA(C4,D4,"d")+1

Para poder calcular cuantos días caen dentro de cada año (2013,2014,2015), vamos a agregar tres columnas en nuestra tablam una para cada año, y en la celda F4 ponemos esta fórmula (que luego copiamos al resto de la tabla):

=SUMAPRODUCTO(--(AÑO($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))))=F$3))

Vamos a "disecar" la fórmula de adentro hacia afuera:

• La expresión "($D4-$C4+1)" calcula el total de días transcurridos entre el principio y el fin de la tarea. Es equivalente al cálculo en la celda E4.
• La expresión INDIRECTO("1:"&($D4-$C4+1)) crea una referencia al rango "1:750". Al usar esta expresión como argumento de la función FILA en forma matricial (al estar dentro de la función SUMAPRODUCTO), obtenemos un vector con los valores 1,2,3,...,750
• Al sumar a $C4 este vector en ($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))), obtenemos un vector de fechas día por día que comienza el 06/07/2013 y termina con el 25/07/2015
• La función AÑO extrae de cada fecha el año correspondiente, por lo que obtenemos un vector de años con 750 puntos.
• Este vector los comparamos con el año que aparece en la celda F$3, por lo que obtenemos un vector de valores VERDADERO o FALSO (si se cumple o no la condición).
• La doble negación a la izquierda de la función AÑO convierta los valores VERDADERO en 1 y los FALSO en 0. Igualmente podríamos haber multiplicado la expresión por 1.
• Finalmente SUMAPRODUCTO suma todos los valores del vector que cumplen con la condición. Recordemos, como puse más arriba, que SUMAPRODUCTO se comporta en forma matricial.

Podemos visualizar el proceso del cálculo usando la herramienta "Evaluar fórmula" en "Fórmulas-Auditoría de fórmulas"

En este video comento el proceso

Calcular el primer día hábil del mes con Excel

Esta nota viene a colación de una consulta sobre cómo poner en una fila (o columna) de una hoja de Excel todos los días hábiles de un mes.

La idea de mi lector era crear una hoja que al activar la hoja, las fechas hábiles aparezcan en un rango determinado.

Excel cuenta con varias funciones para calcular fechas tomando en cuenta feriados: para calcular fechas, DIA.LAB, DIA.LAB.INTL ; para lapsos, DIAS.LAB, DIAS.LAB.INTL.

Para calcular el primer día hábil del mes combinamos las funciones DIA.LAB y FIN.MES de la siguiente manera

En la celda C2 ponemos el número de mes, en la celda C3 el año; en la celda C5 la fórmula

=DIA.LAB(FIN.MES(FECHA(C3,C2,1),-1),1)

Hace el cálculo de esta manera

FIN.MES calcula el último día del mes de la FECHA, tantos meses atrás o adelante como indique el segundo argumento de la función. En nuestro caso "'-1" indica un mes atrás, es decir 31/08/2013.

DIA.LAB calcula el día laboral antes o después de la fecha indicada, según el segundo argumento de la función. En nuestro caso "1" significa el primer día laboral después del 31/08/2013.

Si queremos poner todos los días hábiles del mes corriente en un rango de la hoja hacemos lo siguiente:

• En la celda C1 ponemos la función HOY() y le asignamos el nombre definido "fechaActual" (o cualquier otro que les plazca).
• El mes y el año lo obtenemos en las celdas C2 y C3 con las funciones =MES y =AÑO. A la celda C2 le asignamos el nombre definido "mesActual".
• En la celda C6 ponemos esta fórmula y la copiamos 30 filas abajo (dado que el número máximo de días de un mes es 31)

=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),FILA()-5))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),FILA()-5),"")

La condición de la función SI evalúa si la fecha cae dentro del mes definido ("mesActual"), en caso positivo calcula la fecha del día hábil usando como numerador la expresión FILA()-5 (-5 porque empezamos en la fila 6; en caso de comenzar en otra fila hay que modificar la fórmula).
Si la condición no se cumple, la celda no muestra ningún valor.
Si queremos mostrar las fechas en una fila, usamos como numerador el expresión COLUMNA()-x. Por ejemplo, si la primer fecha aparece en la columna D, la fórmula será

=SI(MES(DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-3))=mesActual,DIA.LAB(FIN.MES(fechaActual,-1),COLUMNA()-3),"")

Una acotación para el final: DIA.LAB tiene un tercer argumento opcional que permite usar una rango para señalar los días festivos, además de los fines de semana.

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.