Hours between two dates

What do I spend the hours between dates of job change?

In writing post #100 of this blog, that will soon reach the 200,000 page views, but really I have written 50 original articles in two versions, English and Spanish.

What is the purpose of this article?

I share with you a public Excel template, that it can be downloaded below, used to obtain the number of hours between two dates, with up to 4 different time ranges and without the condition that the start and end times are within the work hours, allowing you to choose which days of the week are working days (traditional weekend of Saturday and Sunday or just Sunday or any other day).

Next scheme is used to calculate one hour range, extrapolating it is used to calculate different hour ranges, simply adding them.


Who would this template may be useful?

To Human Resources professionals, project managers, budgets managers that calculate manhours efforts or hours-person, part-time workers, the moonlighters, teachers and professors to organize their classes and, in general, those who need to know how many hours dedicated to training, work, leisure, etc..

What inspired me?

Over two years ago I was doing Excel calendars for this blog and my friend Chandoo threw out a challenge: "How many hours did Johnny work? [Excel Homework]"

How I solved the challenge?

I immediately started calculating the hours worked between two dates by Johnny, excluding Saturdays, Sundays and holidays and I got this formula which was very successful in its simplicity:

Being B3 the start date and time and C3 the end date and time, working for nine hours.

Chandoo congratulated me on this formula in his next article "How to Calculate Working Hours Between 2 Dates [Solution]" and a few supporters began to discuss our formulas, including Excel gurus as Daniel Ferry and Chip Pearson.

How to calculate a more general solution?

The above formula and the proposals by Daniel Ferry, Michael, sixseven and Elias had a condition that both start and end hours are within working hours and Chandoo challenge us again to solve a problem that could be even more interesting, get working hours between two dates without the condition that both the start time and end time are within working hours.

I started to collect and analyze all the proposals of my fellow forum Chandoo.org and everything I found on the Web and, not seeing a smart solution, I started to develop for 5 days to get this new formula:
Where:
DecimalHours = $D$29 - $D$28 (number of daily working hours)
$D$28 = Start work hour
$D$29 = End work hour

What makes me take up again the calculation of hours between dates?

Four days ago a query posed by jcascon11 in the AyudaExcel forum:

Calculate time between two dates in hours without holidays or weekends and with a time range

I need to calculate the time between start date and end date excluding holidays, Saturdays and Sundays and this the time range:
Monday to Thursday working hours are from 8:00 to 15:00 and 17:00 to 19:30
Fridays from 8:00 to 15:00
Anybody can help me?


How to calculate the hours over a time range of work?

You can download the file with the calculations based on the version of Excel:
- Column D for Excel 2003 and 2007, using the auxiliary columns E:K
- Column C for Excel 2010 and later, using the new function:
WORKDAY.INTL(start_date, days, [weekend], [holidays])

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string. For example, 0000011 would result in a weekend that is Saturday and Sunday.

In both cases I use variations of the formula I proposed more than two years ago in the Chandoo, but in Excel 2010 you can do it with a single megaformula and in earlier versions of Excel you need auxiliary columns to do the same.

Where do I download the template?

Clicking on these links:


Why not try it yourself in the cloud?

This is the interactive template for those without Excel 2010 and for those want to see the result of the total hours in column C with this version of Excel. You can modify all cells with yellow background.

As Microsoft does not provide data validations in Excel Web App, a mandatory 0 (working day) or 1 (non-working day) must be entered in the range E2 to K5.



How I tested if the calculation of the hours is correct?

Introducing ranges of different days, with the aid of these two formulas that are based on the RAND() function included in the cell A25:B34 for the start date (column A) and end date (column B):
The chosen values are 41275 to 01/01/2013 and 41640 to 01/01/2014.

I compare in the column L the hours calculated in Excel 2010 (column C) with other versions (column D) with the following formula:


Where keep learning to calculate dates and times in Excel?

Although there are many sites like Chandoo's blog, and not bad to begin with the site by Chip Pearson: Dates And Times In Excel

Will I continue writing articles on this blog?

Until now I have to greatly appreciate the welcome that is having this blog among the curious who come to the graphical user interfaces and looking for help to solve their calculations in Excel.

Depending on the time I have, henceforth I will do my best to continue contributing with ideas and examples of these two wonderful worlds, trying to bring aid to my readers. What is certain is that I used to plan and solve problems and order to challenge myself and try to experiment and master a little more each day these waves of calculation and programming.

Horas entre dos fechas

¿En que empleo las horas entre fechas de cambio laboral?

En escribir este artículo, que es el número 100 de este blog, que pronto alcanzará las 200.000 páginas vistas, aunque realmente he escrito 50 artículos originales en dos versiones, española e inglesa.

¿Para qué sirve este artículo?

La plantilla en Excel que publico, y que se puede descargar más abajo, sirve para obtener el número de horas entre dos fechas cualesquiera, con hasta 4 rangos horarios diferentes y sin la condición de que las horas inicial y final estén dentro de los horarios de trabajo, permitiendo elegir qué días de la semana son laborables (fin de semana tradicional de sábado y domingo o solamente domingos o cualquier otro día).

Para un rango o turno horario se emplea este esquema para su cálculo que, extrapolándolo sirve para calcular diferentes rangos o turnos, simplemente sumándolos.



¿A quién le puede servir?

A los profesionales de Recursos Humanos, a los gestores de proyectos y presupuestos, a los gerentes que calculan los esfuerzos en horas-hombre u horas-persona, a los trabajadores a tiempo parcial, a los pluriempleados, a los maestros y profesores para organizar sus clases y, en general, a quien necesite saber cuántas horas dedica a la formación, al trabajo, al ocio, etc.

¿En qué me he inspirado?

Hace más de dos años yo estaba haciendo calendarios en Excel para este blog y mi amigo Chandoo lanzó un reto: "¿Cuántas horas trabajò Johnny?"

¿Cómo resolví el reto?

Me puse inmediatamente a calcular las horas trabajadas entre dos fechas por Johnny, sin contar sábados, domingos ni festivos y obtuve esta fórmula que tuvo mucho éxito por su simplicidad:

Siendo B3 la fecha y hora inicial y C3 la fecha y hora final, para 9 horas laborables.

Chandoo me felicitó por esta fórmula en su siguiente artículo "Cómo calcular las horas trabajadas entre dos fechas" y unos cuántos simpatizantes comenzamos a comentar nuestras fórmulas, entre ellos los gurús de Excel Daniel Ferry y Chip Pearson.

¿Cómo calcular una solución más general?

La fórmula anterior y las propuestas por Daniel Ferry, Michael, sexseven y Elias tenían como condición que tanto las horas de inicio y final están dentro del horario de trabajo y Chandoo nos volvió a retar para resolver un problema que podría ser aún más interesante, obtener las horas de trabajo entre dos fechas dadas sin la condición de que tanto la hora de inicio como la hora final están dentro de los horarios de trabajo.

Me puse a analizar y a recopilar todas las propuestas de mis compañeros del foro Chandoo.org y todo lo que encontré en la Web y, al no ver una solución inteligente, me puse a desarrollarla durante 5 días hasta llegar a esta nueva fórmula:

Siendo:
DecimalHours = $D$29 – $D$28 (número de horas de trabajo diario)
$D$28 = Hora inicial del trabajo
$D$29 = Hora final del trabajo

¿Qué me hace retomar de nuevo el cálculo de horas entre fechas?

Hace cuatro días jcascon11 planteó una consulta en el foro de AyudaExcel:

Calcular tiempo en horas entre dos fechas sin festivos ni fines de semana y con rango horario

Necesito calcular el tiempo entre una fecha inicial y una fecha final sin contar festivos, sábados y domingos y con este rango horario
De lunes a jueves el horario a contar es de 8:00 a 15:00 y de 17:00 a 19:30
Los viernes de 8:00 a 15:00
¿Alguien me puede ayudar?


¿Cómo calcular las horas con más de un rango horario de trabajo?

Puedes descargar el fichero con los cálculos según la versión de Excel:
- Columna D para Excel 2003 y 2007, que usa las columnas auxiliares E:K
- Columna C para Excel 2010 y posteriores, que usa la nueva función:
DIA.LAB.INTL(fecha_inicial; días_lab; [fin_de_semana]; [festivos])

Los valores de las cadenas de fin de semana tienen siete caracteres de longitud y cada carácter de la cadena representa un día de la semana, comenzando por el lunes. 1 representa un día no laborable y 0 representa un día laborable. Sólo se permiten los caracteres 1 y 0 en la cadena. 1111111 no es una cadena válida. Por ejemplo, 0000011 daría como resultado un fin de semana que es sábado y domingo.

En los dos casos empleo variantes de la fórmula que propuse hace más de dos años en el foro de Chandoo, aunque en Excel 2010 se puede hacer con una única megafórmula y con versiones anteriores de Excel hacen falta columnas auxiliares para hacer lo mismo.

¿Dónde descargo la plantilla?

Presionando en este enlace o en el botón de Descarga.

Descarga el fichero desde el icono (Sites Google) o desde el enlace (Microsoft OneDrive)


¿Por qué no lo pruebas en la nube tú mismo?

Esta es la plantilla interactiva para los que no tienen Excel 2010 y quieran ver el resultado del total de horas en la columna C con esta versión de Excel. Se pueden modificar todas las celdas con fondo amarillo.

Como Microsoft no contempla las validaciones de datos en Excel Web App, se debe introducir obligatoriamente un 0 (día laboral) o un 1 (día no laboral) en el rango E2 a K5.



¿Cómo he probado si el cálculo de las horas es correcto?

Introduciendo rangos de días diferentes, con la ayuda de estas dos fórmulas que se basan en la función ALEATORIO() incluidas en las celdas A25:B34 para la Fecha inicial (columna A) y Fecha final (columna B):
Los valores elegidos son 41275 para el 01-01-2013 y 41640 para el 01-01-2014.

En la columna L comparo las horas calculadas en Excel 2010 (columna C) y en otras versiones (columna D) con la siguiente fórmula:


¿Dónde seguir aprendiendo a calcular fechas y horas con Excel?

Aunque hay muchos sitios, como el blog de Chandoo para los que sepan inglés, no está mal comenzar por la web de preparación para opositores de José Enrique Martínez: Cálculos con fechas y horas en Excel

¿Continuaré escribiendo artículos en este blog?

Hasta ahora tengo que agradecer enormemente la buena acogida que está teniendo este blog entre los curiosos que se acercan a los interfaces gráficos de usuario y a los que buscan una ayuda para resolver sus cálculos en Excel.

En función del tiempo que tenga, en adelante haré lo posible por seguir aportando ideas y ejemplos de estos dos maravillosos mundos, tratando de que sirvan de ayuda a mis lectores.  Lo que si es seguro es que a mí me sirven para plantear y resolver problemas y para retarme e intentar experimentar y dominar un poco más cada día estas ondas de cálculo y programación.

How to translate Excel formulas locally

Excel formulas translation

When I search formulas in the blogs of Excel gurus, all are written in English and I can not copy and paste them into my spreadsheets. Sure that ever happened to anyone reading this. Another problem of the same kind occurs when I try to write in English Forum a formula I have written in Spanish language.


In this link you can download the latest version of the translator, by pressing the Download button in the menu at the top of my SkyDrive:

In Holland, the excellent MVP Ron de Bruin wrote an article explaining internationalization how to determine the version and the local language of Excel and geolocated features to consider: rondebruin - international

One of the pages with Excel formulas to learn more is Chandoo.org written in English by the CEO - Chief Excel Officer, Purna Duggirala, whose purpose is to become us formidable Excel users. And it is getting with its excellent items! like this: chandoo.org - formula forensics no 033

Of course I see the formula written in English does not help much and can not enter in a cell to see what happens, for example:

What happens is that you receive an error message when you enter the formula in a version of Excel in Spanish and are not readily available by translating manually each change of its functions and replacing the commas by semicolons, where applicable, to obtain this formula translated into Spanish:

The reverse case is to have the formula written in Spanish and want to translate into English to upload it to an international forum. The forward and backward translation of the formulas will be resolved by my Excel formula translator that you can download for free from this page if you read a little further down.

Tables to translate Excel functions

Do not try to use a table to translate Excel functions from one language to another, such that there are many tables on the Internet, with the added difficulty that each new version of Excel generates function names with their translations to various languages, eg:

This translation process is very laborious and an example of bad productivity, being prone to error (error-prone) and being difficult to verify these bugs (bug-prone), as translated functions not match with the original, operators can be misinterpreted and the arguments passed may be incorrect, which is difficult to correct and very slow to get the exact translation of the formula.

Excel formulas translators

To solve the translation of formulas are suggested several procedures, such as those proposed by George L. Dunkelman in his blog: jldexcelsp - traduccion de funciones excel

I don't forget to mention at David McRitchie who has explained on his website several methods to translate formulas from English into other languages, especially with the use of the Immediate window in the VBE Macro Editor: dmcritchie.mvps.org - excel/language

Formula Translation in English or the Excel local language

I loaded an early version of this translator into the AyudaExcel.com two years ago to share it with my friends on the forum: ayudaexcel.com - traducir formulas al ingles al idioma local excel

In this link you can download the latest version of the translator, by pressing the Download button in the menu at the top of my SkyDrive:

In column A write the apostrophe character (') followed by the formula in English. Write formulas in Spanish directly. (Enter formulas in cells marked in yellow).

Each file columns indicate the following:
A column  Original formula
B column Spanish formula
C column English formula
D column Result formula

In cell A2 I have included the English formula copied from the Chandoo.org page, translated into Spanish appears in cell B2. In cell D2 shows the result of applying the formula, not being correct: #N/A. If you enter a formula in column A in English, will be translated into Spanish but not interpreted correctly, indicating the error: #NAME? (See A7 cell).

The list separator character for Excel function arguments in English is the comma (",") and in Spanish is the semicolon (";"). This separator is not set from Excel options but from the Control Panel of the operating system, entering the Regional and Language Settings, Additional Settings, Custom number formats, and modifying the list separator.

The formula translator is based on two properties of Excel VBA:

The macro code to translate formulas is in the worksheet "Formula":


Office Language Interface Pack (LIP)

I do wish to comment on a method to translate formulas allowing translation between two languages ​​without being one of them English. The downside is that you have to be constantly switching from one language to another to see the translation of formulas.

Going into Excel Options and selecting the language, you can see the languages ​​installed in Office and choose the languages of the Help and the user interface, if it has been added.


To download additional languages, adds new languages ​​from Excel options or visit these pages:
office.microsoft.com - office language interface pack lip downloads
office.microsoft.com - do i need a language pack or language interface pack

To learn more about the location of Excel formulas and functions you can read these articles:
pedrowave.blogspot.com - languages translator in excel


Cómo traducir localmente fórmulas Excel

Traducción de fórmulas Excel

Cuando busco fórmulas en los blogs de los gurús de Excel, todas están en inglés y me resulta imposible copiarlas y pegarlas en mis hojas de cálculo. Seguro que le ha pasado a cualquiera que esté leyendo esto. Otro problema del mismo estilo se me produce cuando intento escribir en un foro en inglés y la fórmula la tengo escrita en español.


En este enlace se puede descargar la última versión del traductor, pulsando en el botón Descargar del menú de arriba en OneDrive:
(Descarga desde Google "Excel Download" o Microsoft OneDrive)


En Holanda, el excelente MVP Ron de Bruin escribió un artículo sobre internacionalización que explica cómo averiguar la versión y el lenguaje local de Excel y las funciones geolocalizadas a tener en cuenta aquí: rondebruin - international

Una de las páginas con las que mas aprendo fórmulas Excel es la de Chandoo que está escrita en inglés por el CEO - Chief Excel Officer llamado Purna Duggirala, cuyo propósito es convertirnos en formidables usuarios de Excel. ¡Y lo está consiguiendo con sus excelentes artículos! como éste: chandoo.org - formula forensics no 033

Claro que ver la fórmula escrita en inglés no ayuda mucho y tampoco se puede introducir en una celda para ver qué pasa, por ejemplo con:

Lo que pasa es que salta un mensaje de error en la fórmula al introducirla en una versión de Excel en español y no es fácil conseguir modificarla manualmente traduciendo cada una de sus funciones y sustituyendo las comas por puntos y comas, donde corresponda, para obtener la fórmula traducida al español:

El caso contrario es tener escrita la fórmula en español y quererla traducir al inglés para subirla a un foro internacional.  La traducción directa e inversa de las fórmulas serán resueltas mediante el traductor de fórmulas Excel que te puedes bajar y descargar gratuitamente desde esta página si lees un poco más abajo.

Tablas de traducción de funciones Excel

No se trata de usar una tabla de traducciones de las funciones de Excel de un idioma a otro, que de esas hay muchas tablas en Internet, con el agravante de que cada versión de Excel genera nuevos nombres de funciones con sus correspondientes traducciones a varios lenguajes, por ejemplo:

Este procedimiento de traducción es muy laborioso y nada productivo, siendo propenso a generar errores (error-prone) y siendo difícilmente verificables estos errores de programación (bug-prone), ya que las funciones traducidas no tienen por qué coincidir con las originales, los operadores pueden ser malinterpretados y los argumentos pasados pueden ser incorrectos, lo que es complicado de subsanar y muy lento hasta conseguir la traducción exacta de la fórmula.

Traductores de fórmulas Excel

Para resolver la traducción de fórmulas hay varios procedimientos, como los que propone Jorge L. Dunkelman en su blog: jldexcelsp - traduccion de funciones excel

No quiero dejar de mencionar a David McRitchie que ha explicado en su página Web varios métodos para traducir fórmulas del inglés a otros idiomas, sobre todo con el uso de la Ventana Inmediato en el Editor de macros VBE: dmcritchie.mvps.org - excel/language

Traductor de fórmulas al inglés o al lenguaje local de Excel

Una primera versión de este traductor la colgué en AyudaExcel.com hace dos años para compartirla con mis amigos del foro: ayudaexcel.com - traducir formulas al ingles al idioma local excel

En este enlace se puede descargar la última versión del traductor, pulsando en el botón Descargar del menú de arriba en SkyDrive:

En la columna A escribe el signo de apóstrofo (') seguido de la fórmula en inglés. Escribe fórmulas en español directamente. (Introduce las fórmulas en las celdas marcadas en amarillo).

Cada una de las columnas del fichero indican lo siguiente:
Columna A Fórmula original
Columna B Fórmula en español
Columna C Formula in English
Columna D Formula resultante

En la celda A2 he incluido la fórmula en inglés copiada de la página de Chandoo.org, que aparece traducida al español en la celda B2. En la celda D2 se muestra el resultado de aplicar la fórmula que, al no ser correcta, resulta: #N/A. Si se introduce una fórmula en inglés en la columna A, se verá su traducción al español pero no la interpretará correctamente, indicando el error: #¿NOMBRE? (Ver la celda A7).

El carácter separador de listas y de argumentos de funciones de Excel en inglés es la coma (",") y en español es el punto y coma (";").  Este separador no se configura desde las opciones de Excel sino desde el Panel de Control del sistema operativo, entrando en la Configuración Regional y de Idioma, en Configuración Adicional, Personalizar formatos de Números, modificando el Separador de Listas.

El traductor de fórmulas está basado en dos propiedades del VBA de Excel:


El código macro para traducir las fórmulas está en la hoja "Formula":


Paquetes de Office de Interfaz de Idiomas

No quiero dejar de comentar un método para traducir fórmulas que permite la traducción entre dos idiomas sin ser uno de ellos el inglés.  El inconveniente es que hay que estar conmutando continuamente entre un idioma y otro para ver la traducción de las fórmulas.

Entrando en las Opciones de Excel y eligiendo la opción de Idioma, se pueden ver los idiomas instalados de Office y elegir los idiomas de la Ayuda y de la interfaz de usuario, siempre que se hayan agregado.


Para descargar idiomas adicionales, agrega nuevos idiomas desde las opciones de Excel o visita estas páginas:
office.microsoft.com - office language interface pack lip downloads
office.microsoft.com - necesito un paquete de idioma o un paquete de interfaz de idiomas

Si quieres saber más sobre la localización de fórmulas y funciones de Excel puedes leer estos artículos:
pedrowave.blogspot.com - traductor de 58 idiomas en excel


Mi Lista de Blogs- My Blog List