How to Calculate Time in Excel

Sometimes when you enter a time value in the cell of your table in excel, you face some problems. here we are going to explain some of them and how to solve these problems.

Before start to explain this subject, it is better to understand how excel store time values.

In the following picture we shows that;

the value of 12:00 AM is zero, and then for every 6 hours, excel adds -0.25 to the value of the cell, for instance, the value of 12:00 PM is equal to 0.5.

How to Enter Time in The Cell in Excel;

The first problem which people face when they enter time in the cell is they forget to add space between time and AM or PM

How to Calculate Duration Time;

Sometimes as you can see in below picture, you have time of departure and arrival in two column of your table, and you want to calculate the duration of the travel.

You can see when you calculate the subtraction of two numbers. the answer is the Hour but you need Time.

For that you can follow these steps;

1- Select the cells in the column.

2- Right-click on it and choose “Format Cell”.

3- In the “Number” tab, click on the “Custom” and Choose “[h]:mm” type and click on Ok.

Now you can see in the third column, we have a duration of time.

How to Calculate Duration Time When It is Crossing from Midnight;

As you see, when one specified time in the cell shows the time before midnight and the other shows the time after midnight we will have this problem.

For solving that you can use this formula;

=Mod((D5-C5),1)

In this formula, “D5” and “C5” are the addresses of the Departure and arrival cells in the table.

How to Calculate Sum of Time in Excel;

Some times you have a table and you want to calculate the sum of the activities but when the result is higher than 24 hours you will have a problem for that you can follow these steps;

First Metod

1- Right-click on the result cell.

2- In the number tab click on “Custom” and then choose “[h]:mm” type and click on “Ok”.

Now you can the result with the correct value.

Second Metod

1- Right-click on the cell.

2- Click on the “Format cells…”

3- Click on the “Number” tab.

4- Click on the “Time” category.

5- In the location part click on “United State”.

6- Choose “37:30:55” type.

7- Click on Ok.

Now you can see the result with the correct value.

How to Round Time in Excel;

In this example, in the first column, we have the “date and time of starting the activity” and in the second column, we have the “dates and times of the ending activities”.

Here we are going to explain how to calculate the difference between these times and then round them in 15 min, one hour and days.

For calculating the difference between these times, you can easily use from this formula;

=C4-B4

Here “C4” and “B4” are the addresses of the cells.

If you want to have these values in hours you should change the format.

For that, you should follow these steps;

1- Select the column and Right-click on the cells.

2- In the Number tab click on the Time category and select “37:30:55”.

3- Click on Ok.

How to Round Time to the Nearest 15 Min in Excel;

For rounding time to the nearest 15 min you can use this formula;

=MROUND(D4,“0:15”)

Here “C4” is the address of the cell which you want to round its value.

How to Round Time to the Nearest Hour in Excel;

For rounding time to the nearest hour you can use this formula;

=MROUND(D4,“1:00”)

Here “C4” is the address of the cell which you want to round its value.

How to Round Time to the Nearest day in Excel;

For rounding time to the nearest day you can use this formula;

=MROUND(D4,“24:00”)

Here “C4” is the address of the cell which you want to round its value.

If you want to see these value in days you should follow these steps;

1- Right-click on the cells.

2- Click on the format cells.

3- In the number tab choose “General”.

4- Click on Ok.

How to Calculate Time Multiple Currency.

Sometimes when you want to calculate the salary of the employees. you should multiple “Time worked” by “Rate/Hour”.

But the answer is not correct.

Here we are going to explain this subject.

If you have an hour in one column and in other column currency.

You should follow this formula;

=D3* C3*24

Here “D3” and “C3” are the address of the cells which we want to multiply them by together.

If you have minutes instead of the hours, you should follow this formula;

=D3* C3*24*60

Here “D3” and “C3” are the address of the cells which we want to multiply them by together.