Excel y años bisiestos

lunes, abril 09, 2007

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: bisiesto

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





Technorati Tags:

12 comments:

Anónimo,  10 abril, 2007 21:17  

HOLA JORGE:
COMENTARTE QUE ESTOY TRATANDO DE DESARROLLAR UN ARCHIVO QUE ME PERMITA HACER UN CONTROL DE LA CADENA LOGISTICA, DESDE LA RECEPCION HASTA LA DSISTRIBUCION, LA CONSULTA ES SI TU CONOCES O SABES DE PAGINAS DE LAS CUALES PUEDA EXTRAER MODELOS GUIA PARA DESARROLLAR MI PROPIO MODELO PARA LA GESTION DE CADENA LOGISTICA GRACIAS.
MARCELO

Jorge L. Dunkelman 10 abril, 2007 22:44  

Hola Marcelo,
no conozco ninguna página que ofrezca un modelo de esa naturaleza. Pero supongo que algo podrás encontrar con Google.
De todas maneras no te recomiendo usar Excel para esa tarea. Puedes usar Excel para desarrollar un "prototipo", un borrador que te ayude a aclarar ideas. Pero para el manejo de una cadena logística necesitas un programa con una base de datos sólida.

Francesc 13 abril, 2007 14:03  

Hola amigo Jorge, buceando en la red encontré tu blogg ayer. es justamente lo que andaba buscando, claridad, calidad y dinamismo.

soy ingniero agrónomo y te escribo desde España. voy a leer tus post anteriores a ver si encuentro el modo de hallar la ecuación de ajuste sin necesidad de hacer el gráfico (en ec. linales es con interseccion.eje y pendiente.eje) pero en otros tipos de ajuste no se cómo se podría hacer...y después, para la ecuación allada, forzar un valor de "y" y resolver sin necesidad de pasar cada vez el solver y simpre de forma automática, sólo variando la tabla inicial que se recalcule todo. en lineal ya lo he echo, pero claro, sólo es aislar.

perdon por el rollo, sin más me despido agradeciendote el tiempo y la ayuda que prestas a esta tu comunidad de lectores.

atentamente,

Francesc

Franprats@gmail.com

Pablo Andrés,  05 agosto, 2007 03:33  

Jorge cordal saludo, mira con respecto al la formulación que planteas =FECHA(AÑO(B4);MES(B4)+1;0) funciona perfectamente en todas las hojas menos una en particular generándome el error #!Num¡. No sé porque razón en las otras hojas si funciona pero en esta en particular insiste en generar el error. No puedo obviar esta hoja ya que tengo un trabajo extenso en dicha hoja.

Agradezco si con tu experiencia me puedas ayudar.

Jorge L. Dunkelman 05 agosto, 2007 20:59  

Hola Pablo,

el error #NUM puede deberse a varios motivos, como usar un argumento no númerico en una función que lo requiere. Me parece que ese puede ser el tu caso. Fijate si la fecha que usas como argumento es realmente un número (las fechas en Excel lo son) o un texto que se ve como fecha.

Anónimo,  06 agosto, 2007 05:29  

Jorge antes te deseo bendiciones en tu viaje y feliz regreso, agradezco tu respuesta oportuna, mañana probaré alternativas; la verdad ya había verificado si la entrada era numérica y lo es. Me tiene de los cabellos ya que estoy diseñando una plantilla bastante compleja en cuanto a formulación y en uno de los módulos necesito generar las fechas de los periodos correspondientes de una amortización de un crédito que puede ir hasta 30 años. Requiero las fechas para su posterior causación contable identificando el periodo efectivo de la realización de estos. Logré definir la fórmula para tal fin como sigue y funciona como te comento en todas las hojas menos una (la que imprescindiblemente requiero):

donde G15 es la fecha del primer pago y
q el número de pagos por año

=FECHA(AÑO(G15);MES(G15)+(12/q);
MIN(DIA($G$15);DIA(FECHA(AÑO(G15);
MES(G15)+(12/q)+1;0))))

¿Porqué razón en una de las hojas genera #¡Num! y en las otras funciona?

Jorge L. Dunkelman 06 agosto, 2007 19:07  
Este comentario ha sido eliminado por el autor.
Jorge L. Dunkelman 06 agosto, 2007 19:09  

Mandame el archivo a jorgedun@gmail.com

Pablo Andrés,  09 agosto, 2007 09:39  

Gracias Jorge ya te envié el archivo a tu correo (jorgedun@gmail.com) como me lo solicitaste. Lo reduje drásticamente para mayor enfoque en la problemática.

Saludos y bendiciones.

Anónimo,  10 marzo, 2012 04:51  

Hola jorge:
una consulta, la celda C5 del año bisiesto en formato condicional como puedo hacer que la celda se ponga de color amarillo, osea solo los años bisiesto.

Gracias un placer saludarte.

Jorge L. Dunkelman 10 marzo, 2012 09:50  

Usando la fórmula que muestro en la nota, de la siguiente manera:

=O(RESIDUO(B1;400)=0;Y(RESIDUO(B1;4)=0;RESIDUO(B1;100)<>0))

Esta fórmula da VERDADERO si el número en la celda B1 cumple con las condiciones de año bisiesto o FALSO si no las cumple.

Si al celda B1 contiene una fecha tendrías que modificar la fórmula de esta manera:

=O(RESIDUO(AÑO(B1);400)=0;Y(RESIDUO(AÑO(B1);4)=0;RESIDUO(AÑO(B1);100)<>0))

Esra fórmula sólo funciona para fechas posteriores al 01/01/1900

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP