Cálculo de días específicos con Excel

El cálculo de fechas con Excel es muy flexible y ofrece muchas posibilidades. Para ejemplificar esto, responderé en esta nota a tres preguntas que me hace un lector del Perú:

1.Como podría hacer para calcular cuantos sábados y/o domingos hay en un mes dado el primer y ultimo día.

2.Para saber que día cae un segundo o tercer domingo de un mes X, por ejemplo el día del padre o de la madre.

3.Calcular el día de Pascua (Domingo Santo)


Calcular la ocurrencia de un día determinado.

Para calcular cuantas ocurrencias tiene un día determinado de la semana en un mes (o cualquier otro período) usamos esta fórmula

={SUMA(SI(DIASEM(A2-1+FILA(INDIRECTO("1:"&TRUNCAR(B2-A2)+1));2)=C2;1;0))}

Esta es una fórmula matricial y se pone en la celda apretando simultáneamente Ctrl + Mayúscula + Enter




En la celda A2 ponemos la fecha del primer día del mes/período, en la celda B2 el último día del mes/período, en C2 el número de orden del día de la semana (1 = Lunes, 2= Martes, etc). Es decir si queremos saber cuántos domingos hay en un período, ponemos 7 en la celda C2.

Calcular la fecha de un día determinado, dado el mes y el año.
Si queremos calcular, por ejemplo, la fecha del tercer viernes de abril de este año, usamos esta fórmula

=FECHA(E2;D2;1+((C2-(B2>=DIASEM(FECHA(E2;D2;1);2)))*7)+(B2-DIASEM(FECHA(E2;D2;1);2)))

donde B2 contiene el día buscado (1 = Lunes, 2 = Martes, etc); C2 contiene el número de orden dentro del mes; D2 contiene el mes y E2 el año



También aquí usamos DIASEM con el argumento Tipo = 2.

Calcular la fecha de Pascua con Excel.

Este cálculo es más complicado y mostraré aquí tres posibilidades

1 – una función definida por el usuario (UDF), citada por Chip Pearson


Public Function EasterDate(Yr As Integer) As Date

Dim d As Integer
d = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
EasterDate = DateSerial(Yr, 3, 1) + d + (d > 48) + 6 - ((Yr + Yr \ 4 + _
d + (d > 48) + 1) Mod 7)

End Function

Esta función hay que copiarla a un módulo del editor de Vba, y luego a activa con el asistente de fórmula (categoría: definidas por el usuario)



2 – con la fórmula =MULTIPLO.INFERIOR(FECHA(A3;5;DIA(MINUTO(A3/38)/2+56));7)-34

donde A3 contiene el año

3 – con esta fórmula, citada por John Walkenbach, sobre la cual ni él, y mucho menos yo, tenemos la más mínima idea de cómo funciona

=MONEDA(("4/"&A3)/7+RESIDUO(19*RESIDUO(A3;19)-7;30)*14%;)*7-6

Nuevamente A3 contiene el año.






Technorati Tags: MS Excel

Como convertir números en palabras en Excel en Español

Un compañero de trabajo me preguntó si había alguna función en Excel para convertir números a palabras(expresar los números en letras). Es decir, si el la celda A1 pongo 123,45 en la celda B1 donde pongo la función aparece "ciento veintitrés pesos con cuarenta y cinco centavos". Una rápida búsqueda en Google me llevó a está página de la base de datos de conocimientos de Microsoft sobre Cómo convertir un valor numérico en palabras en Excel en inglés que es una traducción automática del articulo How to convert a numeric value into English words in Excel.
Esta UDF (user defined function = función definida por el usuario) de Microsoft tiene varios problemas:
1 – sólo sirve para convertir números al inglés
2 – sólo usa el dólar como moneda
3 – sólo usa el punto como separador de decimales.

Para adaptar esta función a los usuarios de Excel en español, tuve que introducir varios cambios. Por ejemplo, en la mayoría de los países hispano parlantes se usa la coma como separador de decimales. Pero en Guatemala, Honduras, República Dominicana, México, Nicaragua, Puerto Rico y El Salvador, se usa el punto, como en los Estados Unidos.
También la moneda cambia de acuerdo al país.

Aquí pueden descargar una adaptación de la función para convertir números al español, que toma en cuenta todos estos problemas.



Para usarla hay que copiar el código de la función a un módulo del editor de Vba, por ejemplo en el Personal.xls. Luego podemos usar el asistente de funciones,




Esta versión de la función tiene tres argumentos, la celda que contiene el número (MyNumber), el separador (Sep) y la moneda (Mon)



El resultado:




Technorati Tags: MS Excel

Excel y años bisiestos

Si queremos construir una tabla que muestre el primer y último día de cada mes como ésta



usamos estas fórmulas:

en la celda B4 ponemos =FECHA(B1;1;1), que nos da el primero de Enero del año que aparece en la celda B1

en la celda B5 ponemos =FECHA(AÑO($B$4);MES(B4)+1;1) y la copiamos a todo el rango B6:B15

en la celda C4 ponemos =FECHA(AÑO(B4);MES(B4)+1;0) y la copiamos a todo el rango C4:C15.

Para calcular el último día del mes podemos usar también =EOMONTH(B4;0), si tenemos instalado el Anaysis Toolpak.

Si cambiamos el año en la celda B1 veremos que Excel toma en cuenta los años bisiestos para calcular el último día de Febrero.

Excel usa el calendario gregoriano para los cálculos que involucran fechas. De acuerdo al calendario gregoriano, cada año que es divisible por 4 es un año bisiesto. Pero como la duración del año solar es 365,25635, existe un pequeño error que hay que corregir. Para esto agregamos a la regla que un año que es divisible por 100, es bisiesto sólo si también es divisible por 400. Por este motivo el 1800,1900 o 2100 por ejemplo, no son años bisiestos.

Podemos establecer si un año es bisiesto o no, usando esta fórmula:

=SI(O(RESIDUO(B1;400)=0;Y(RESIDUO(B1;4)=0;RESIDUO(B1;100)<>0));"Año Bisiesto"; "Año no bisiesto")

Podemos agregar esta fórmula a nuestra tabla para enriquecer la información que presenta:



Descarga del ejemplo:

Fuente: XL: Método para determinar si el año es bisiesto

Technorati Tags: MS Excel