Der Blog für digitale Kompetenz

Der Blog für digitale Kompetenz

Calculate with date, time and working days in Excel

Sometimes the simplest things you can do with Excel are not as easy as it first seems. This still includes the right calculation with times and dates. If then both come together and you want to determine, for example, the number of days or hours that are between two values, this raises repeatedly questions.

It gets even more complex if not just a total number of days, but especially only working days should be calculated. In our tutorial, we go through several scenarios on how to easily work with these values in Excel.

Calculate with date and time in Excel

Topic Overview

Anzeige

Calculate with date, time and working days in Excel

Sometimes the simplest things you can do with Excel are not as easy as it first seems. This still includes the right calculation with times and dates. If then both come together and you want to determine, for example, the number of days or hours that are between two values, this raises repeatedly questions.

It gets even more complex if not just a total number of days, but especially only working days should be calculated. In our tutorial, we go through several scenarios on how to easily work with these values in Excel.

Calculate with date and time in Excel

Topic Overview

Anzeige

1. Calculate with date in Excel

1. Calculate with date in Excel

Before we start to calculate dates and times (German spelling) in Excel, it is very interesting to know how Excel handles this data at all. Because the time starts here not in the year 0 but only on 01.01.1900. And that’s exactly how Excel translates a date that was not formatted as a date.

Example:
If you enter a date in a cell before 01.01.1900 (ex .: 31.12.1899) then it does not matter if you format the cell as a date value or as an absolute number. It will always display exactly the specified value, which Excel does not interpret as a date or as a number, but as a text. So you can not calculate this.

It looks different if the value you enter, for example, is 01/01/1900 (or later). Then Excel will automatically recognize this input and format the value as a date. Now change the formatting of the cell in question to an absolute number then the 01.01.1900 would be there as 1 and every day after that comes with 2, 3, 4 … etc.

Because it is the 1st or 2nd or 3rd day after the 01.01.1900. And that’s exactly how Excel calculates dates later.

See picture (click to enlarge)

So funktioniert Zeitrechnung in Excel
cshow
Ads

When it comes down to it, it’s actually easy to calculate with dates. Let’s say you were born on 01.07.1985 and would like to know how many days you are old. Then enter the first cell 01.07.1985 and in the second cell the current date bsp. 06.11.2019.

Then you only have to subtract the smaller date (01.07.1985) from the larger one (06.11.2019) in the result cell. Or you use the function = DAYS(   in the result cell (the result is 12,546 days in both cases).

See picture (click to enlarge)

Wie alt bin ich in Tagen in Excel
cshow
Ads

Likewise, you can work on this way, of course, only with times.

Before we start to calculate dates and times (German spelling) in Excel, it is very interesting to know how Excel handles this data at all. Because the time starts here not in the year 0 but only on 01.01.1900. And that’s exactly how Excel translates a date that was not formatted as a date.

Example:
If you enter a date in a cell before 01.01.1900 (ex .: 31.12.1899) then it does not matter if you format the cell as a date value or as an absolute number. It will always display exactly the specified value, which Excel does not interpret as a date or as a number, but as a text. So you can not calculate this.

It looks different if the value you enter, for example, is 01/01/1900 (or later). Then Excel will automatically recognize this input and format the value as a date. Now change the formatting of the cell in question to an absolute number then the 01.01.1900 would be there as 1 and every day after that comes with 2, 3, 4 … etc.

Because it is the 1st or 2nd or 3rd day after the 01.01.1900. And that’s exactly how Excel calculates dates later.

See picture (click to enlarge)

So funktioniert Zeitrechnung in Excel
cshow
Ads

When it comes down to it, it’s actually easy to calculate with dates. Let’s say you were born on 01.07.1985 and would like to know how many days you are old. Then enter the first cell 01.07.1985 and in the second cell the current date bsp. 06.11.2019.

Then you only have to subtract the smaller date (01.07.1985) from the larger one (06.11.2019) in the result cell. Or you use the function = DAYS(   in the result cell (the result is 12,546 days in both cases).

See picture (click to enlarge)

Wie alt bin ich in Tagen in Excel
cshow
Ads

Likewise, you can work on this way, of course, only with times.

2. Calculate combined with date and time

2. Calculate combined with date and time

Of course, to combine the date and time to present a result works well. Let’s take our example to hand again and just say you were born on 01.07.1985 at 8:00 clock and today it is the 06.11.2019 at 16:05 clock.

Here we would like to know how many days, hours and minutes you are old. If we proceed the same way as before in calculating only days and try to get a result in days, minutes and hours, we will fail. Because the formatting dd : hh: mm unfortunately fails in Excel as soon as the result goes beyond 31 days.

The solution for this we do with the function = rounding off (

Enter the following function in the cell in which the result should be:

= ROUND (O9-N9; 0) & IF (ROUND (O9-N9; 0) = 1; “Day”; “Days”) & HOUR (O9-N9) & “Hours,” & MINUTES (O9-N9) & MINUTE (O9-N9) & “Min.”

See picture (click to enlarge)

Mit Datum und Uhrzeit kombiniert in Excel rechnen

You can already see that you get a satisfying result with a few small tricks. In the end, of course, you could extend the function by a few seconds, and also other texts as “Min.” or “hrs.” use. But in principle it is feasible in this way.

Of course, to combine the date and time to present a result works well. Let’s take our example to hand again and just say you were born on 01.07.1985 at 8:00 clock and today it is the 06.11.2019 at 16:05 clock.

Here we would like to know how many days, hours and minutes you are old. If we proceed the same way as before in calculating only days and try to get a result in days, minutes and hours, we will fail. Because the formatting dd : hh: mm unfortunately fails in Excel as soon as the result goes beyond 31 days.

The solution for this we do with the function = rounding off (

Enter the following function in the cell in which the result should be:

= ROUND (O9-N9; 0) & IF (ROUND (O9-N9; 0) = 1; “Day”; “Days”) & HOUR (O9-N9) & “Hours,” & MINUTES (O9-N9) & MINUTE (O9-N9) & “Min.”

See picture (click to enlarge)

Mit Datum und Uhrzeit kombiniert in Excel rechnen

You can already see that you get a satisfying result with a few small tricks. In the end, of course, you could extend the function by a few seconds, and also other texts as “Min.” or “hrs.” use. But in principle it is feasible in this way.

3. Calculate with working days in Excel

3. Calculate with working days in Excel

If we now explicitly want to calculate only with working days instead of just a total number of days and exclude weekends and public holidays, Excel offers the functions “WORKING DAYS”, “WORKING DAYS.INTL”, “NETWORKING DAYS” and “NETWORKING DAYS INTL”.

“WORKING DAYS”:
Function “working days” does not calculate the working days, but an end date inclusive weekend, which bsp. could also be a term of payment.

“WORKING DAYS INTL”:
The additional function “INTL” stands for international, and is the claim that in other countries, unlike in Germany also on Saturday and Sunday possibly worked, and is free for other days. You can thus choose within the function which days should be weekends.

“NETWORKDAYS”:
Function “Net working days” calculates the working days, between a start and an end date including weekends.

“NETWORK DAYS INTL”:
Again, the additional function “INTL” for International.

Let’s just take the example that we want to calculate a number of working days or net working days for a project. A small list of days off we refer to when the function asks for “days off”.

See picture (click to enlarge)

Function working day

Funktion Arbeitstag in Excel

Function working day INTL

Funktion Arbeitstag INTL in Excel

Function net working days

Funktion Nettoarbeitstage in Excel

Function net working days INTL

Funktion Nettoarbeitstage INTL in Excel

We hope that we have been able to help you with this tutorial and that you will now be able to work more reliably with date and times in Excel.

Blogverzeichnis Bloggerei.de

If we now explicitly want to calculate only with working days instead of just a total number of days and exclude weekends and public holidays, Excel offers the functions “WORKING DAYS”, “WORKING DAYS.INTL”, “NETWORKING DAYS” and “NETWORKING DAYS INTL”.

“WORKING DAYS”:
Function “working days” does not calculate the working days, but an end date inclusive weekend, which bsp. could also be a term of payment.

“WORKING DAYS INTL”:
The additional function “INTL” stands for international, and is the claim that in other countries, unlike in Germany also on Saturday and Sunday possibly worked, and is free for other days. You can thus choose within the function which days should be weekends.

“NETWORKDAYS”:
Function “Net working days” calculates the working days, between a start and an end date including weekends.

“NETWORK DAYS INTL”:
Again, the additional function “INTL” for International.

Let’s just take the example that we want to calculate a number of working days or net working days for a project. A small list of days off we refer to when the function asks for “days off”.

See picture (click to enlarge)

Function working day

Funktion Arbeitstag in Excel

Function working day INTL

Funktion Arbeitstag INTL in Excel

Function net working days

Funktion Nettoarbeitstage in Excel

Function net working days INTL

Funktion Nettoarbeitstage INTL in Excel

We hope that we have been able to help you with this tutorial and that you will now be able to work more reliably with date and times in Excel.

Blogverzeichnis Bloggerei.de

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Search for:

About the Author:

Michael W. SuhrDipl. Betriebswirt | Webdesign- und Beratung | Office Training
After 20 years in logistics, I turned my hobby, which has accompanied me since the mid-1980s, into a profession, and have been working as a freelancer in web design, web consulting and Microsoft Office since the beginning of 2015. On the side, I write articles for more digital competence in my blog as far as time allows.

Search by category:

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Popular Posts:

102, 2024

Integrate and use ChatGPT in Excel – is that possible?

February 1st, 2024|Categories: Artificial intelligence, ChatGPT, Microsoft Excel, Microsoft Office, Shorts & Tutorials|Tags: , , , |

ChatGPT is more than just a simple chatbot. Learn how it can revolutionize how you work with Excel by translating formulas, creating VBA macros, and even promising future integration with Office.

2008, 2023

Internet Addiction – A serious look at a growing problem

August 20th, 2023|Categories: Google, Homeoffice, Shorts & Tutorials|Tags: , , |

Internet addiction is just as serious an illness as alcohol or drug addiction. Just that this is a mental illness. In this article we want to go into the phenomenon in more detail and provide assistance.

607, 2023

Main keyboard shortcuts in Windows 10/11

July 6th, 2023|Categories: Homeoffice, Microsoft Office, Shorts & Tutorials, Windows 10/11/12|Tags: |

Entdecken Sie die wichtigsten Shortcuts in Windows 11, um Ihre Produktivität zu steigern. Von allgemeinen Befehlen bis hin zu spezifischen Fenster-Management- und System-Shortcuts, lernen Sie, wie Sie mit diesen Tastenkombinationen effizienter arbeiten können.

107, 2023

Encrypt USB stick – These options are available

July 1st, 2023|Categories: Shorts & Tutorials, Data Protection, Hardware, Homeoffice, Mac OS, Windows 10/11/12|Tags: , , |

Protecting sensitive data is essential. Encrypting USB sticks provides an extra layer of security. Whether it's built-in software, operating system features, third-party software, or hardware encryption, there are numerous options.

Offers 2024: Word & Excel Templates

Special Offers 2024: Word Design CV-Templates

Monthly Technique Bestsellers:

Bestseller 2022-2023 WLAN-Heizkoerperthermostate

SmartHome | Energy & Security

SmartHome | Energy & Security

Bestseller 2022-2023 WLAN-Heizkoerperthermostate
Bestseller 2022-2023 Notebooks

PC & Accessoires

PC & Accessoires

Bestseller 2022-2023 Notebooks
Bestseller 2022-2023 Smartphones

Smartphone & Accessoires

Smartphone & Accessoires

Bestseller 2022-2023 Smartphones

Title

Ads

Popular Posts:

Search by category:

Autumn Specials:

Anzeige
Go to Top