Fechas vacaciones

Archivo de Excel utilizado: FechasVacaciones.xlsx

Cómo saber si se solapan las vacaciones de los trabajadores de una empresa.

Mostramos un caso donde se ve de forma gráfica si los periodos vacacionales se solapan o no.

Pulse la tecla F9 de recálculo manual para ver cómo cambia la simulación.

Se ve repetido el informe. Arriba todas las barras son azules. Abajo se ha marcado la zona de solapamiento en color naranja.

Los colores salen usando Formato condicional. La fórmula del Formato condicional para la celda F3 es la siguiente.

• =Y(F$1>=$D3;F$1<=$E3)

Si se cumple esa condición se pondrá de color azul. Luego se copia o extiende ese formato a todo el rango F3:VN17.

Para los colores de la tabla inferior usamos dos Formatos condicionales para la celda F20 que son los de las dos fórmulas siguientes.

• Para el azul  =Y(F$1>=$D20;F$1<=$E20)
• Para el naranja  =Y(F$1>=$D20;F$1<=$E20;SUMA(F$3:F$17)>1)

Luego se copia o extiende el formato condicional al rango F20:VN34.

Días por meses

Archivo de Excel utilizado: dias_por_meses.xlsm

Dada una fecha inicial y una fecha final vamos a calcular cuantos días hay en cada mes de entre los meses de ese intervalo. Por ejemplo, entre el 20-enero-2016 y el 5-abril-2016 el resultado sería el siguiente:

• para enero-2016: 12 días
• para febrero-2016: 29 días ya que es bisiesto
• para marzo-2016: 31 días
• para abril-2016: 5 días

En este cálculo se incluyen los dos extremos, esto es, se incluye el día inicial y el día final. El número de días totales es de 77 que se obtienen en Excel restando el día final memos el día inicial y sumando 1. Se ha de sumar 1 ya que hemos decidido incluir los dos extremos.

Esto se puede calcular con fórmulas de Excel, lo cual hacemos en la Hoja2 de archivo proporcionado, pero también hemos querido crear una función personalizada, una macro, que calcule esto.

La función es la siguiente 

=dias_del_mes(fecha inicial;fecha final;mes)

Tiene tres argumentos, los dos primeros son obvios, y en el tercero se ha de indicar una fecha correspondiente al mes que queremos analizar. Da igual la fecha que usemos, lo importante es el mes. Así, por ejemplo, si deseamos calcular los días correspondientes al mes de enero-2016 pondremos cualquier fecha válida de ese mes, tanto da poner el día 1, el 31 o cualquier otro de enero de 2016.

La macro es la siguiente.

Function dias_del_mes(fechaInicio, fechaFin, fechaMes)
Dim inicioMes As Date
Dim finMes As Date
finMes = Application.WorksheetFunction.EoMonth(fechaMes, 0)
'veamos una forma curiosa de calcular el inicio de mes correspondiente a fecha_mes
inicioMes = Application.WorksheetFunction.EoMonth(finMes - 45, 0) + 1
If fechaInicio >= inicioMes And fechaInicio <= finMes Then
  If Year(fechaInicio) = Year(fechaFin) And Month(fechaInicio) = Month(fechaFin) Then
    dias_del_mes = fechaFin - fechaInicio + 1
  Else
    dias_del_mes = finMes - fechaInicio + 1
  End If
ElseIf fechaInicio < inicioMes And fechaFin > finMes Then
  dias_del_mes = finMes - inicioMes + 1
ElseIf fechaFin >= inicioMes And fechaFin <= finMes Then
  dias_del_mes = fechaFin - inicioMes + 1
Else
  dias_del_mes = 0
End If
End Function

Trabajar con fechas y horas

Archivo de Excel utilizado: excelavanzado_fechas.xlsx

Excel permite trabajar bastante bien con fechas y horas. Veamos algunas funciones que permiten este tratamiento.

Primer Vídeo

Tratamiento de fechas.

Segundo Vídeo

Tratamiento de horas, minutos y segundos.

Tercer Vídeo

Determinación del día de la semana.

Cuarto Vídeo

Calendario perpetuo.

Para saber más

Si quiere ampliar otros aspectos del tratamiento de fechas y horas puede visitar estos enlaces.

• Calcular el tiempo entre dos fechas
• Sumar horas en Excel

Graficar por fechas

Archivo de Excel utilizado: excelavanzado_graficar_fechas.xlsm

Vamos a realizar una macro que nos permite representar gráficamente una serie de valores de una tabla pudiendo elegir el intervalo de fechas. El usuario selecciona una fecha inicial y una fecha final que van en el eje horizontal, y automáticamente el gráfico se adapta a ese intervalo de fechas.

Sub Eje_Personal()
ActiveSheet.ChartObjects("Temporal").Activate
ActiveChart.Axes(xlCategory).MinimumScale = [F4]
ActiveChart.Axes(xlCategory).MaximumScale = [F8]
End Sub

Graficar según el diccionario de la Real Academia Española

Sumar horas en Excel

Descargar el fichero: sumar_horas.xlsx

Para sumar horas y minutos en #Excel cuando la suma supera las 24 horas no debemos emplear el formato clásico de hh:mm sino este otro [h]:mm

En el ejemplo que estamos manejando en el fichero sumar_horas.xlsx deseamos calcular las horas semanales trabajadas por un empleado en jornadas de mañana y tarde.

Para introducir las horas de inicio y final de jornada lo haremos en Excel escribiendo por ejemplo 8:00 para indicar las ocho de la mañana, y 17:15 para indicar las cinco y cuarto de la tarde.

La fórmula de la celda H4 es la siguiente:

=(E4-D4)+(G4-F4)

Los paréntesis no son necesarios. Los hemos puesto para separar la jornada de mañana y la jornada de tarde.

Las celdas H9, H10 y H11 contienen todas ellas la misma fórmula que es la suma de las horas trabajadas durante la semana. La fórmula es la siguiente:

=SUMA(H4:H8)

La diferencia entre las tres fórmulas está en el formato que hemos empleado. El formato correcto es el de la celda amarilla H9. Esto es así, ya que cuando la suma de horas supera las 24 horas, se añade un día y si usamos un formato donde no se ve ese día, únicamente vemos la fracción de horas.

En este caso 35 horas y media es lo mismo que 1 día y 13 horas y media.

Tiempo de Respuesta

Descargar el fichero: TiempoRespuesta.xlsm

Una macro en Excel que calcula el tiempo de respuesta de un servicio técnico, excluyendo domingos y festivos. Se trata de una empresa que proporciona el servicio técnico a sus clientes. Se pretende controlar cuántas horas se tarda en proporcionar el servicio, desde que se toma nota del aviso hasta que queda finalmente solucionado por el técnico que acude hasta las instalaciones del cliente. Deseamos que en el cómputo de horas no se incluyan los domingos, ni las fiestas según un listado de festivos que incluimos en la hoja de cálculo.

La macro que implementamos se crea en forma de función, y es la siguiente.

Código:

Function TR(Inicio As Date, Fin As Date, Optional Festivos As Range) As Double
Dim i As Long
Dim domingo As Integer
Dim c As Variant
Dim F As Long
Dim Fiesta As Boolean
If Festivos Is Nothing Then
  'si en TR no se usa el tercer argumento optativo
  If Int(Inicio) = Int(Fin) Then
    'si es el mismo día
    TR = Fin - Inicio
  Else 'para más de un día
    TR = 1 + Int(Inicio) - Inicio 'lo del primer dia
    TR = TR + Fin - Int(Fin) 'lo del último día
    For i = Int(Inicio) + 1 To Int(Fin)
      'contamos los domingos
      If i Mod 7 = 1 Then domingo = domingo + 1
    Next i
    TR = TR + (Int(Fin) - Int(Inicio)) - domingo - 1
  End If
  'si Inicio o Fin son domingo entonces TR=0
  If Int(Inicio) Mod 7 = 1 Or Int(Fin) Mod 7 = 1 Then TR = 0
Else
  'si en TR se usa el tercer argumento optativo
  If Int(Inicio) = Int(Fin) Then
    'si es el mismo día
    TR = Fin - Inicio
  Else
    For i = Int(Inicio) + 1 To Int(Fin)
      'detecta si el día i es fiesta
      Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If i = F Then Fiesta = True: Exit For
      Next
      'si el día i es fiesta o es domingo se descuenta de TR
      If Fiesta Or i Mod 7 = 1 Then TR = TR - 1
    Next i
    TR = TR + Fin - Inicio
  End If
  'si Inicio o Fin son domingo entonces TR=0
  If Int(Inicio) Mod 7 = 1 Or Int(Fin) Mod 7 = 1 Then TR = 0
  'si Inicio es fiesta entonces TR=0
  Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If Int(Inicio) = F Then Fiesta = True: Exit For
      Next
      If Fiesta Then TR = 0
  'si Fin es fiesta entonces TR=0
  Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If Int(Fin) = F Then Fiesta = True: Exit For
      Next
      If Fiesta Then TR = 0
End If
End Function

Disponemos de una fórmula antigua que únicamente calculaba la diferencia entre la fecha y hora del aviso y la del momento de atención. La nueva fórmula elimina los domingos y las fiestas incluidas en cierto rango que se marca al introducir la fórmula TR.

Calcular el tiempo entre dos fechas

Descargar el fichero: SiFecha.xlsm

Para calcular el tiempo transcurrido entre dos fechas, podemos restar ambas fechas y nos dará los días transcurridos. Se ha de poner la celda donde se efectua la resta en formato celda GENERAL. Excel utiliza un sistema para trabajar con fechas que consiste en asignar a cada fecha un número correlativo comenzando el 1-enero-1900. Por eso al restar dos fechas nos da el número de días transcurridos entre una y otra.

Hoja1

En Excel existe una fórmula, no documentada en la ayuda y que no figura en el asistente de funciones, denominada:

=sifecha(fecha inicial;fecha final;tipo)

donde:

tipo es el tipo de respuesta que da la función, y puede ser:

• y Calcula el número de años transcurridos
• m Calcula el número de meses transcurridos
• d Calcula el número de días transcurridos. Equivale a restar ambas fechas
• ym Calcula los meses sin considerar los años enteros transcurridos
• md Calcula los días sin considerar los años y meses enteros transcurridos

Hoja2

En ésta hoja hemos creado unos controles numéricos que el usuario puede ir modificando para ir aumentando o disminuyendo los años, meses y días.

Podemos calcular la edad en años de una persona, o podemos calcular la antiguedad en la empresa de un trabajador.

Se puede detectar un error de la fórmula tal y como se ve en la celda C7, que aparece de color rojo cuando difiere de la celda D18.

Otro aspecto curioso de esta hoja es la fórmula programada:

=DisplayCellFormula(Celda)

que nos da la fórmula de la celda que se indique.

Código:

'Función que muestra la fórmula de una celda
'Devuelve la fórmula que contiene una celda en lenguaje local
'Si se quita la palabra 'Local' devuelve la fórmula en inglés.
Function DisplayCellFormula(Celda As Range) As String
DisplayCellFormula = Celda.FormulaLocal
End Function

Edad

Para determinar la edad de alguien podemos poner su fecha de nacimiento en la celda A1 y emplear la siguiente fórmula:

=SIFECHA(A1;HOY();"Y")&" Años, "&SIFECHA(A1;HOY();"Ym")&" Meses, "&SIFECHA(A1;HOY();"Md")&" Dias""

La función =HOY() nos da la fecha del sistema.

Último día del mes

Para determinar el último día del mes de febrero de 2008, podemos emplear esta fórmula:

=FECHA(2008;3;1)-1

o bien, esta otra:

=FECHA(2008;3;0)

Observar, que el día que se pone es CERO.

El resultado es: 29/02/2008

y así nos damos cuenta de que el año 2008 es bisiesto.

Actualización

En versiones más recientes de Excel ya disponemos de una función que calcula el último día del mes. Se llama:

=FIN.MES(fecha_inicial;mes)

Si en el argumento mes ponemos cero nos estamos refiriendo al mes de la fecha_inicial, y si ponemos 2 nos referimos a dos meses más respecto a la fecha_inicial.

Por ejemplo, si usted escribe la siguiente función de función sabrá cuantos días tiene el mes actual.

=FIN.MES(HOY();0)

La función =HOY() nos da la fecha del sistema. Si su ordenador está correctamente puesto en fecha la función HOY proporciona el día actual.