Cálculo de días en base a meses de 30 días

Uno de los usos más difundidos de Excel es el cálculo del número de días entre dos fechas. Excel permite hacerlo de diversas maneras, con una simple resta (fecha final – fecha inicial) o usando funciones como DIAS.LAB o DIA.LAB y las nuevas funciones de fechas (Excel 2010) DIAS.LAB.INTL y DIA.LAB.INTL.

Todos los cálculos de fechas en Excel toman en cuenta la duración de cada mes en particular, incluidos los años bisiestos.

Pero sucede que hay ocasiones en las cuales necesitamos hacer el cálculo de días entre dos fechas basándonos en meses de 30 días (año de 360 días). Debo confesar que en la práctica no me ha tocado hacer cálculos de ese tipo, pero si a uno de mis lectores que debía hacer cálculos para pagar aguinaldos.

La forma más sencilla e inmediata de hacerlo es usando la función DIAS360. Esta función tiene dos argumentos obligatorios y una opcional:

• Fecha inicial
• Fecha final
• Método: según la ayuda en línea de Excel

• FALSO u omitido Método de EE.UU. (NASD). Si la fecha inicial es el último día del mes, se convierte en el día 30 del mismo mes. Si la fecha final es el último día del mes y la fecha inicial es anterior al día 30, la fecha final se convierte en el día 1 del mes siguiente; de lo contrario la fecha final se convierte en el día 30 del mismo mes.

• VERDADERO Método europeo. Las fechas iniciales o finales que corresponden al día 31 del mes se convierten en el día 30 del mismo mes.

Ejemplo del uso

Sumamos 1 a la función para incluir en la cuenta tanto la fecha de inicio como la del final.

Otra forma de hacer el cálculo, si nos gusta la vida complicada, es usar la "indocumentada" función SIFECHA.

Esta fórmula dará el mismo resultado

=SIFECHA(A2;B2;"m")*30+SIFECHA(A2;B2;"md")+1

La expresión SIFECHA(A2;B2;"m") da como resulta 2, el número de meses entre las dos fechas (por eso usamos el argumento "m" en la función). Al multiplicar por 30 obtenemos el número de días en base a meses de 30 días.

La expresión SIFECHA(A2;B2;"md") da el número "extra" de días por encima del número de meses calculado. En nuestro caso dará 10 por lo que, como en el caso anterior, sumamos 1 a nuestra fórmula.

Encontrar el primer número positivo o negativo en el rango

En el pasado hemos tratado el tema de encontrar el último valor en un rango y también el último positivo o negativo.

Para encontrar el primer valor negativo en un rango podemos usar esta fórmula:

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0))

Para hallar el primer número positivo en el rango usamos

=INDICE($A$2:$A$15,COINCIDIR(VERDADERO,INDICE($A$2:$A$15>0,0,1),0))

Sencillamente invertimos el signo "<" a ">"

La fórmula funciona de esta manera:

La expresión INDICE($A$2:$A$15<0,0,1) genera un vector de valores VERDADERO o FALSO

Luego  COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0) nos da la ubicación de la primera aparición de VERDADERO en el vector

El resultado lo usamos como argumento en la función INDICE "externa"

obteniendo así el resultado

Podemos, también, obtener la dirección de la celda que contiene el valor combinando la función COINCIDIR con la función DIRECCION

=DIRECCION(COINCIDIR(VERDADERO,INDICE($A$2:$A$15<0,0,1),0)+1,1)

Listas desplegables dependientes – inicialización de celdas con eventos

El tema “listas desplegables dependientes” es uno de los más populares entre los lectores de este blog. Las 17 notas bajo esta etiqueta contienen, al día de hoy, 529 comentarios. Si bien los comentarios son una excelente herramienta de comunicación con mis lectores, muchos aportes y soluciones quedan “enterrados” allí y de hecho no están a disposición de los lectores.

En esta nota expongo el tema de la inicialización de celdas que contienen listas desplegables dependientes, tema que ha sido desarrollado en varios comentarios en distintas notas.

Empecemos por exponer el problema. Supongamos un modelo donde tenemos dos celdas con listas desplegables dependientes (en este caso, con la opción Lista de Validación de Datos)

Como podemos ver, al elegir el continente (América del Norte) en la celda C3, la lista desplegable dependiente en la celda C4 nos muestra sólo los países de ese continente. El problema reside en que al elegir otro continente, el país elegido anteriormente queda en la celda hasta que sea reemplazado.

Esto puede generar errores por lo que necesitamos que al cambiar la elección del continente, el contenido de la celda C4 sea borrado.

Esto lo hacemos programando un evento Worksheet_Change para la hoja que contiene las listas desplegables

Private Sub Worksheet_Change(ByVal Target As Range)
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        Range("celPais").ClearContents
    End If

End Sub

Este evento se dispara cuando se produce un cambio en la celda C3; el código elimina el contenido de la celda C4.

Usamos nombres definidos para las celdas para facilitar la lectura del código y para evitar tener que corregirlo si cambiamos la ubicación de las listas desplegables en la hoja

• celContinente =eleccion!$C$3
• celPais =eleccion!$C$4

Otra variante del tema, como me consulta uno de mis lectores, es que exista un valor por defecto cuando se elige el continente.

Para que en la celda “país” aparezca el primer país en la lista del continente elegido ponemos este evento Worksheet_Change en la hoja correspondiente

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strRangeName As String
 
    If Union(Target, Range("celContinente")).Address = _
                            Range("celContinente").Address Then
        If Len(Range("celContinente")) = 0 Then
            Range("celPais").ClearContents
            Exit Sub
        End If
     
        Call valDefault
    End If

End Sub

y esta macro en un módulo común del editor Vb

Sub valDefault()
    Dim n As Name
    Dim strRangeName As String
 
    strRangeName = WorksheetFunction.Substitute(Range("celContinente"), " ", "_")

    Range("celPais") = WorksheetFunction.Index(Range(strRangeName), 1)
     
End Sub

En esta macro es necesaria ya que al crear los nombres que se refieren a los rangos con las ciudades, los espacion entre las palabras han sido reemplazados por “_” (por ejemplo, América del Sur se transforma en America_del_Sur). Lo mismo hemos hecho en la regla de validación de datos, como ya hemos explicado en notas anteriores

En este modelo, al borrar el contenido de la celda C3 (continente), se elimina automáticamente el valor de la celda C4 (país); al elegir un continente, aparece el primer país en la lista correspondiente; el país puede ser cambiado luego.

Para que aparezca algún otro país como valor por defecto, la posibilidad más obvia es ponerlo en el primer lugar de la lista.

El cuaderno con el ejemplo puede descargarse aquí.

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.