Duraciones II.

La duración la calculo restando la fecha de finalización (FFin, columna C) y la fecha de inicio (FInicio,columna B). Es una resta, y como tal, la formula sería C2-B2, pero como no todas las actuaciones están acabadas, utilizo la función si para discriminar las que están acabadas de las que no. Lo hago tanto en la columna D(cabecera VALIDA) como en E (cabecera Duración HH:MM)

A	B	C	D	E	F	G
CODPRO	FINICIO	FFIN	Válida	Duración (h:mm)	Duración (Num)	Duración (Min. Texto-Valor)
45	01/11/07 04:00	02/11/07 11:37	=SI($C2>$B2;1;0)	=SI(C2>B2;C2-B2;0)	=$E2*$D2	=SI(C2>=B2;VALOR(TEXTO($F2;"[m]"));0)
10	01/11/07 08:30	01/11/07 15:06	1	6:36	0,275000000	396,000000000
44	01/11/07 09:01	02/11/07 18:17	1	33:16	1,386111111	1996,000000000
21	01/11/07 10:15	01/11/07 20:03	1	9:48	0,408333333	588,000000000
30	01/11/07 10:19	01/01/1901	0	0:00	0,000000000	0,000000000
20	01/11/07 10:41	02/11/07 17:03	1	30:22	1,265277778	1822,000000000
20	01/11/07 10:41	02/11/07 17:03	1	30:22	1,265277778	1822,000000000

Duraciones.

Una empresa de ámbito nacional realizo una serie de trabajos en el año 2007, de los que dispongo del código de provincia donde se realizaron, de la fecha de inicio y de una fecha de filalización de los trabajos. El supuesto objetivo del libro excel es obtener el número de actuaciones concluidas y su duración media por provincia.

Utilizo las funciones:
SI
VALOR
TEXTO
DECIMAL
BUSCAR
INDICE
SUMAR.SI
COINCIDIR

Los datos que tenemos son como los de la siguiente tabla :

CODPRO	FINICIO	FFIN
45	01/11/07 04:00	02/11/07 11:37
10	01/11/07 08:30	01/11/07 15:06
44	01/11/07 09:01	02/11/07 18:17
21	01/11/07 10:15	01/11/07 20:03
30	01/11/07 10:19	01/01/1901
20	01/11/07 10:41	02/11/07 17:03
20	01/11/07 10:41	02/11/07 17:03

Calendario perpetuo. ¿Es mejorable este trabajo?

Por supuesto. En la entrada anterior así se insinúa. Podemos optimizarlo.


Podemos prescindir de la plantilla lineal y trabajar solo con la plantilla semanal. Podemos utilizar la función INDICE en vez de la función INDIRECTO.

Aunque este calendario no es una hoja muy cargada de fórmulas podemos aligerar el contenido en fórmulas de la plantilla semanal.

Cada celda de la plantilla semanal se corresponde con un día, que puede ser del mes deseado, del mes anterior, o del mes posterior. Para no presentar días de otros meses utilizo la función SI (fórmula =SI(MES($G$3+1)=$B$2;DIA($G$3+1);""). La celda G3 es el la fecha que se corresponde con el primer día de la primera semana de la plantilla.

En la primera semana de esta plantilla solo los seis primeros días pueden ser de otro mes, el séptimo es necesariamente del mes deseado. Por tanto solo necesitamos la función condicional SI en los seis primeros días. Las semanas segunda, tercera y cuarta no se van a corresponder, en ningún caso, con días de otros meses, por lo que solo hay que incrementar, para cada celda, el valor de la celda anterior. La quinta semana si puede corresponderse con días de otro mes, por lo que hay que utilizar la función SI y de la sexta semana solo hay que considerar los dos primeros días, eso si con la función SI.

Por último podemos preguntarnos como conocer fechas anteriores a 1900. El calendario vigente actualmente en casi todo el mundo es el gregoriano. Este calendario fue adoptado paulatinamente a lo largo de cuatro siglos por los distintos países, por lo que no podemos fácilmente hacer un calendario perpetuo y universal. En España está vigente desde octubre de 1582.

Mi trabajo se basa en el sistema de fechas de excel, que es gregoriano, por lo que como máximo puedo utilizarlo hasta 1582 (parte de octubre, noviembre y diciembre de 1582).

Cada cuatrocientos años se completa un ciclo de años bisiestos. Supongo que el año deseado + 400 se comporta igual que el año deseado. Por tanto si un año es menor que 1900 sumo, en la celda C3 400, mediante la fórmula =SI($B$1<1900;$B$1+400;$B$1).

Para terminar hay que ocultar la línea 3 y las líneas 15 a 26 que son las que contienen el nombre de los meses y proteger la hoja. Una vez protegida la hoja solo se podra escribir en las celdas B1 y B2.

Calendario perpetuo. (2)

Funciones:
Dia(fecha)
Mes(fecha)
Indirecto(dirección, como texto)
Indice()

La plantilla lineal es incomoda y poco vistosa, por lo que una presentación final mas cómoda y con mejor presencia puede ser la de la foto.

En la línea 19, celda B18, sitúo el nombre del mes. Para ello parto de una lista (A6-A17) con los nombres de los 12 meses del año. Para obtener el nombre del mes en cuestión utilizo, en este caso, la función INDIRECTO. Esta selección se puede hacer entre otras con la función INDICE.
Utilizo la fórmula =INDIRECTO("A" & B2+5) para seleccionar el nombre del mes. El parámetro de  la función indirecto es el texto de una dirección de hoja excel . Esta función convierte el texto de la dirección el el valor de la dirección. En nuestro caso, los nombres de los meses están entre las celdas A6 y A17. El número del mes está en la celda B2. Para "Enero" la celda sería "A6". Es decir número de mes mas 5 (B2+5). Para este caso la función INDICE sería: =INDICE(A6:A17;B2), que creo es un poco mas sencilla de utilizar.
Las celdas B20:H24 las dedico a presentar los distintos días del mes. Para presentar solo los días del mes pedido y no los del mes anterior o posterior, utilizo la función condicional SI.
Cada celda del rango B20:H24 se corresponde con una celda del rango B5:AQ5 (plantilla lineal del calendario). Para la celda C21, que se corresponde con la celda J5,la formula utilizada es:
=SI(MES(J$5)=$B$2;DIA(J$5);"") , que traducido, significa "si el mes de J5 (de la celda correspondiente a C21) es igual al mes pedido (b2) devuelve el día del mes de J5 y si no devuelve un blanco"
El paso intermedio de la plantilla lineal se puede omitir, pero las fórmulas se complican. A veces un paso intermedio "despeja la mesa" y nos facilita el trabajo. Como ejemplo
 =SI(MES($B$3-$C$3+5)=B2;DIA($B$3-$C$3+5);"")
En la presentación de la cabecera del mes podriamos incluir, ademas del mes, el año. ( =INDICE(A6:A17;B2) & " " & B1)
Todas estas formulas son compatibles con open office.

Calendario perpetuo (según wikipedia) 1

Este calendario nos da los días del mes pedido, tanto el número de días, como su situación dentro de la semana. Algunos calendarios presentan la semana de domingo a sábado. La semana  que utilizo en el calendario empieza en lunes y acaba domingo.




Bajo este supuesto utilizo las funciones:


Fecha(año;mes;día)


DiaSem(fecha;tipo)


Incremento de fecha mediante la suma de una constante.


Como máximo un mes tiene 31 días. De estos treintaiuno, veintiocho ocupan cuatro semanas. Los tres restantes pueden pertenecer a dos semanas distintas, un día en una y dos en la otra.


La plantilla de un mes genérico debe tener seis semanas. En la líneas 4 y 5 de la hoja excel, aunque en la foto no se vean, hay una plantilla lineal de seis semanas.


En la celda B1 coloco el año, en la celda B2 coloco en número del mes. En la celda B3 calculo el primer día del mes, mediante la fórmula =FECHA($B$1;$B$2;1). En la celda C3 calculo el día de la semana (con un valor de 0 a 6), semana de lunes a domingo, mediante la formula =DIASEM($B$3;2)-1. Podía haber utilizado =DIASEM($B$3;3), pero en su momento opté por hacerlo así.


La celda B5 tiene la primera fecha de la plantilla del calendario. Esta primera fecha se debe corresponder con un lunes, puesto que así lo hemos decidido. Nuestro 1/mm/aaaa no siempre será lunes. Si nuestro día uno fuese martes, la plantilla de nuestro mes debería empezar el la celda C5. Si fuese miércoles debería empezar en la celda d5, etc. Para conseguir ese desplazamiento del día uno lo que hago es restar a ese día uno el día de la semana.


En la celda B5, inicio de las fechas en la plantilla, utilizo la fórmula =$B$3-$C$3 (día uno del mes menos día de la semana). En el resto de la línea de fechas de plantilla incremento en uno el valor de la celda de la izquierda.(C5=B5+1,D5=C5+1,...). Este incremento también se puede realizar C5=B5+1,D5=B5+2,E5=B5+3, etc..

Cálculo del último día del mes. Número de días del mes.

Bajo el supuesto del título utilizo las funciones DIA y FECHA.

Año 2100, en la celda B1
Mes 4, en la celda B2
Primer día del mes 01/04/2100, en la celda b3. Para calcular el primer día del mes solo hay que aplicar la formula =FECHA(B1;B2;1)

El último día del mes lo calculo mediante la formula =B3+31-DIA(B3+31). Al sumar 31 a cualquier inicio de mes, si el mes tiene 31 días nos vamos al primer día del siguiente mes. Si fuese un mes de 30 días nos vamos al día 2. Si fuese de 29 o 28 días nos vamos al día 3 o 4.

Si a la fecha resultante de sumar 31 al primer día del mes le restamos el día del mes, el resultado es el último día del mes (función DIA).

Si queremos saber el número de días del mes, solo hay que aplicar la función DIA al ultimo día del mes.
=DIA(B3+31-DIA(B3+31))

Estas fórmulas son totalmente compatibles con open office.

Sistema de fechas en Excel

Microsoft Excel almacena los datos como números secuenciales denominados valores de serie. De manera predeterminada, la fecha 1 de enero de 1900 es el número de serie 1 y la fecha 1 de enero de 2008 es el número de serie 39448, porque es 39.448 posterior al 1 de enero de 1900. Excel almacena las horas como fracciones decimales, ya que la hora se considera como una porción del día.

Las fechas y las horas son valores y, por lo tanto, pueden sumarse, restarse e incluirse en otros cálculos. Puede ver una fecha como un valor de serie y una hora como una fracción decimal, cambiando el formato de la celda que contenga la fecha o la hora a formato General.

Dada la complejidad de las reglas que gobiernan la manera en que el programa de cálculo interpreta las fechas, éstas deben escribirse de la manera más específica posible. Así obtendrá el nivel de precisión más elevado en los cálculos de fechas.

Lo anterior está copiado directamente de la página de microsoft http://office.microsoft.com/es-es/excel/HP052006743082.aspx

Open office. Calc y Excel.

Para los que no lo sepan, open office es un paquete ofimático GRATUITO, disponible para windows en  http://es.openoffice.org/. Tiene una compatibilidad muy elevada con microsoft office (no al 100%). Yo creo que cumple con el 100% de las necesidades que un trabajo ofimático "normal" pueda necesitar, por lo que no es necesario dejarse una pasta en microsoft office. Este paquete viene incorporado en los  SS.OO. linux, que es un sistema operativo gratuito. Yo he tenido en mis manos uno de los  PC equipados con el S.O.  GUADALINEX, versión del S.O. linux de la junta de Andalucía y para el uso al que están destinados, es mas que suficiente.

Las funciones estudiadas en las entradas anteriores para excel, valen perfectamente para open office. Además open office no presenta el error que presenta excel para el año 1900.

Fórmula =xx=yy

En la entrada sobre bisiestos tengo, entre otras la siguiente, fórmula:
=Residuo($a4;4)=0

Esta fórmula, así escrita, equivale a preguntar si residuo($A4;4) es igual a cero. Genéricamente podemos considerar que la fórmula (=xx=yy) nos compara dos terminos. Estos terminos pueden ser variables o constantes, o una función como en el caso del ejemplo de bisiestos. Si los dos terminos son iguales devuelve el valor lógico VERDADERO. En caso contrario devuelve FALSO.

Bisiestos

La característica principal de año bisiesto (ver wikipedia) es la de tener 366 días en vez de los habituales 365 días.



La regla para los años bisiestos según el calendario gregoriano es:

Un año es bisiesto si es divisible por 4, excepto el último de cada siglo (aquel divisible por 100), salvo que este último sea divisible por 400.

He preparado un libro excel en el que bajo el cáculo de si un año es bisiesto o no se estudian las siguientes funciones:

Función lógica Si(Condición;respuesta si verdadero;respuesta si falso)
Función de manejo fechas Dia(fecha) . Devuelve el día del mes de la fecha.
Función Residuo()
Función Entero()
Función lógica Y():Devuelve verdadero si todos los x son verdaderos. De lo contrario devuelve falso.
Función lógica O(x1,x2,x3...): Devuelve verdadero si al menos uno de los x es verdadero.
Función lógica NO(x):Devuelve el valor negado de x. Falso si x es verdadero y verdadero si x es falso.
Función de manejo fechas Fecha(Año;mes;día)

El cálculo se realiza de dos maneras diferentes, una de ellas ateniendonos a las condiciones indicadas anteriormente y otra utilizando las propiedades del sistema de fechas de excel.

En la columna C de la hoja Bisiestos he calculado, mediante la función Residuo, si el año en cuestión es divisible por 4, mediante la formula:
=RESIDUO($A2;4)=0
Esta formula devuelve un verdadero o un falso si el residuo es cero o si no lo es.

En la columna D calculo si el año es divisible por cien mediante la formula:
=ENTERO($A2/100)*100=$A2
El algoritmo de la formula es tomar la parte entera de dividir un número A por un número B y el resultado multiplicarlo por B. En el caso de que A sea divisible por B el resultado del algoritmo será A y si no es divisible será distinto de A. La fórmula devuelve un verdadero o un falso.

En la columna E calculo si el año es divisible por 400 mediante la función RESIDUO, ya comentada.

En la columna F calculo si un año es bisiesto o no mediante el algoritmo :
Es bisiesto si es divisible por 400. Si es divisible por cuatrocientos es año siempre es bisiesto. Si es divisible por cuatro no lo debe ser por cien. La divisibilidad por cuatro la tenemos con un verdadero o un falso en la columna C. La divisivilidad por cien la tenemos en la columna D, pero como nos interesa que NO sea divisible por 100, es decir que nos de VERDADERO cuando no sea divisible por cien utilizo la formula NO(D2).
La formula en la columna F es =SI(O(Y(C2;NO(D2));E2);-1;0). En este caso, quiero que la función SI devuelva -1 en el caso de que sea verdadero y un 0 en el caso de que sea falso. Podía haber elegido cualquier otro par de respuestas, tipo "Si-No". Son respuestas que libremente indica el usuario.

Si utilizamos las propiedades del sistema de fechas de excel, si al uno de enero de un año determinado le sumamos 365 días nos da el uno de enero de año siguiente si el año no es bisiesto, o el 31/12 si el año lo es. Si en vez de partir del 1/1 partimos del 1/2 y le sumamos 28 días nos debe dar 29/2 si el año es bisiesto o uno del tres si no lo es.

En la columna B utilizo la formula =SI(DIA(FECHA(A2;1;1)+365)=1;0;-1).
El sistema de fechas de excel solo es válido a partir de 1900, pero falla para el año 1900.

En la columna H utilizo la formula =FECHA(A2;2;29). (año;mes de febrero;dia=29). Esta formula da 29/2/año para los años bisiestos y 1/3/año para los que no lo son. En la columna I =SI(DIA(H2)=29;-1;0) pregunto si el día es 29 y si lo es devuelve -1 y si no un 0. Es una opción mas.

Hace muchos años ...

Hace muchos años hice un curso de formación como profesor colaborador. Intentaré acordarme de las normas recibidas y que mis comentarios sobre "El Libro Sobre Excel Que No Escribiré Nunca" queden lo mas claros posibles.