Date and Time Calculation in Excel | Excel Semi-Pro

# Date and Time Calculation in Excel

by on August 31, 2010

The unit of time in Excel is the Day. By using the NOW Function to show both date and time, you can see the underlying serial number by changing the cell format to General.

This serial number is composed of two parts: the date part is the integer and the time part is the decimal.

The Date serial number is an integer value based upon a Date System, either Windows or Mac. The Time serial number is represented as a decimal fraction because time is considered a portion of a day.

## The Date and Time Components

In the table below I entered a date in cell B2, a time in C2, and a formula in D2 that adds the two together. Excel automatically formatted this cell using a m/d/yyyy hh:mm format. I changed the row 3 cell formatting to General so you can see the serial number values.

You can clearly see that Date is a integer value, Time is a decimal fraction, and the Date/Time format has both together in one number.

## Date and Time Calculations

In Excel, date serial number 1 is Jan 1, 1900 for the Windows Date System. The date serial number 40364 represents July 5, 2010 because it’s 40,363 days after Jan 1, 1900. These date serial numbers are used in calculations by Excel.

Time is a decimal value from zero (0) to 0.99999999, representing times from 0:00:00 (midnight) to 23:59:59 (11:59:59 pm).

Think about time values as the number of seconds past 12:00 AM divided by the number of seconds in a day, 86,400. Obviously this can be simplified if using only hours or minutes. For example, the time value for 6:00 AM is 0.25, which is 6hrs divided by 24hrs.

The time shown above (7:12:30 AM) has a decimal value of 0.300347222. The calculation is:

• 7 hours = 7 hrs x 60 min/hr x 60 sec/min = 25,200 seconds
• 12 minutes = 12 min x 60 sec/min = 720 seconds
• Total seconds = 25,200 + 720 + 30 = 25,950
• Decimal value = 25,950 / 86,400 = 0.300347222

Change the cell formatting to Time and you will see 7:12:30 AM (or your local Time setting format).

Tip: When subtracting two different times not in the same day, the integer portion of the serial number must be used or the calculation will be invalid.

bperumal October 16, 2010 at 8:44 pm

xls format

Misty Martinez December 7, 2010 at 1:44 pm

I need to find out if you can help me with a formula to conduct a spreadsheet calculating the number of hours between 2 different dates and times? Is this possible?

Gregory December 7, 2010 at 2:17 pm

Construct a spreadsheet as follows: Cell A1 = Date 1, cell A2 = Date 2, cell A3 = Hours. (the labels)

Format cells A2 and B2 by selecting the cells and using keyboard shortcut Ctrl+1 to bring up the Format Cells dialog box, select the Number tab, and under Category: click Custom, then type m/d/yyy h:mm AM/PM into the text box below Type: and click OK.

In cell C2 enter the formula =(B2-A2)*24

Date 1 has to come before Date 2, so enter something like 12/6/2010 6:00 AM into cell A2, then enter something like 12/7/2010 2:00 PM into cell B2, and you’ll see cell D2 show the value 32. I formatted this cell in a Number format with showing one decimal place.

I’ll write a post about this on Thursday to explain why this works and an easier way to enter the date and times. Hope this helped.

Misty Martinez December 7, 2010 at 2:35 pm

what if i need the date and time in 2 different boxes

ex–A1 start date B1 start time C1 end date and D1 end time

Will this formula still work?

Gregory December 7, 2010 at 3:05 pm

You have to add the date and time together to get what’s known as a serial date. So given your example, the formula is =((C1+D1)-(A1+B1))*24 to get the number of Hours. In words that’s ((End Date + End Time) -(Start Date + Start Time) ) * 24 hours.

JEET February 11, 2011 at 2:59 am

it work only when you have to calculate within 24 hrs

Misty Martinez December 7, 2010 at 3:22 pm

YOU ARE AWESOME!!! THANK YOU SO MUCH

Enayat December 8, 2010 at 11:19 pm

Hi,
How to calculate hours which falls between two days as follows:
From Sunday 12:00AM To Monday 12:00AM which should be 24 hours.
If it is from Sunday 12:00AM to Sunday 11:59PM,the formula in the cell formatted as hh:mm results correctly. For Example: 11:59PM – 12:00AM=23.59 but from 12:00AM to 12:00AM which is expected a total of 24 hours confuses me how to apply to formula since the times don’t refer to the same day or date.
12:00am – 12:00am+1 or *12 also doesn’t work!
Thanks for your quick solution.

Gregory December 9, 2010 at 7:54 am

What you’re trying to do includes two different dates. It just so happens that tomorrow I’m posting an article on how to calculate the number of hours between two dates and times. Suffice to say here that you have to include the serial date with the time, and then subtracting the two will give you an answer in days 1.

What you propose is actually the same thing that happens when you subtract two dates like 9 Dec, 2010 minus 8 Dec, 2010 which would equal one (1) because the default time for every date is 12:00 AM, that is technically 00:00:00. Since the unit of time is “the Day” and there are 24 hours in a day, your answer is 24 hours.

All will become clear in tomorrow’s article. Until then read the comments for this article where @Misty wanted to know how to calculate time between two different dates.

Enayat December 10, 2010 at 7:59 pm

Your are great! Mr.Gregory,
According to your comments, we must enter the date along with the time, right?
I tried your formula and it is almost what I wanted. I am trying it in different situations to see if it is applicable.Will be back in case further help is needed.
Thanks a lot.

Gregory December 10, 2010 at 8:14 pm

Glad to be of service.

JEET February 11, 2011 at 2:53 am

A B
02.02.2011 10:00 05.02.2011 10:00
I WANT MY ANSWER (B-A) IN HH:MM

Gregory February 12, 2011 at 8:59 am

I was completely off-base with my previous answers. All you have to do is format the (B-A) cell with a custom time format [h]:mm.

JEET February 11, 2011 at 2:56 am

A-02.02.2011 10:00
B-05.02.2011 17:00

Santhosh Shetty March 8, 2012 at 3:47 am

Hii i want to calculate the Time difference.
Input Values are
Start Date 13-Mar
Start Time 2.30 pm
End Date 15-Mar
End Time 10.05 am
total time ???????

Gregory March 8, 2012 at 5:05 pm

The formula is: Hours = ((End_Date+End_Time)-(Start_Date+Start_Time))*24
to get a number in hours. I wrote about it in this blog post. You get 43.583 hours.

Another way to do this is to let Excel interpret the time and change the cell time formatting to reflect hours and minutes. To do that enter this formula: (End_Date+End_Time)-(Start_Date+Start_Time) and you’ll get 1.8159722 for a result, it’s a serial date/time. Next use Control+1 (or Command+1 on a Mac) to bring up the Format Cells dialog box, select Custom in the Category pane, then in the Type: box enter [h]:mm and click OK. The cell will show 43:35 which is 43 hours and 35 minutes.

Santhosh Shetty March 22, 2012 at 9:40 pm

Thanks its very easy!.. ;lOnce again thanks for the updating regading time related issue!…

chris March 22, 2012 at 12:58 pm

Hi, I’m not sure if this is the right place, but I am working in a table and have a date field with hours and minutes: 11/17/2011 8:48:44 AM. I’m trying to create a new column to return the same value but with the times removed, so the result is 11/17/2011. Know of a way to do this? Day just gives me “17,” but I want the month and year too.

Gregory March 22, 2012 at 3:49 pm

@ chris, Assume your date/time (11/17/2011 8:48:44 AM) is in cell A2. You can get the date a couple of ways. 1) Enter the formula =DATE(YEAR(A2),MONTH(A2),DAY(A2)) and this will give you the date only. 2) Enter the formula =INT(A2) and change the cell formatting to Date.

chris March 23, 2012 at 9:33 am

Gregory, this was tremendously helpful. Thank you!

Santhosh Shetty March 22, 2012 at 9:42 pm

Hii How to put password for the defined cell or a column.

Gregory March 23, 2012 at 1:55 pm

There are two things you have to do to password protect a defined cell or column. Lets start with step 2, protect sheet. There are two option when you protect a sheet or cell, or range. You can allow users of the workbook to either 1) select unlocked cells, or 2) select both unlocked cells and locked cells. To see these options depends on the version of Excel you are using. For Excel 2010, 2007 (Windows) you choose Review from the Ribbon, then click Protect Sheet. In Excel 2003 (Windows) and Excel 2011 (Mac) you choose Tools > Protection > Protect Sheet. The protection works even if you don’t set a password.

Now, the cells or range you want to protect has to be selected and you then right-click and choose Format Cells from the pop-up box, and click on the Protection tab. Here you have an option for Locked. The default is that this box is checked. Meaning that all cells in the worksheet will be locked if you password protect the sheet. If you uncheck this box, the cells you selected will be available to users when you protect the sheet. All other cells will be locked to them after you protect the sheet.

Then you go back and protect the sheet. If you use a password, make sure you remember it. Otherwise you’ll have a big problem.

Santhosh Shetty March 24, 2012 at 12:20 am

In 2010 excell having pivot version is different than the earlier. I changed to old version but i dont know how i done.. In old version after clicking piviot wizard the header value is pasted in left column but in new version same header value is pasted in right side column ansd same is automatically get updated in left side column. Pls suggest how to go about old version which is very easy..

Gregory March 24, 2012 at 12:50 pm

@Santhosh Try this: Right click on your pivot table, select PivotTable Options from the pop-up menu, click the Display tab, then check the box: Classic PivotTable layout (enables dragging of fields in the grid).

Siti June 7, 2012 at 8:44 pm

dear,

How am I to put hour and minutes word in the formula?
Eg :43.58 become 43 hour 58 min

thank you in advance

Gregory June 7, 2012 at 9:42 pm

Assuming your number 43.58 is using the period as a decimal number and not a time format, and assuming the value is in cell A2, here’s a formula that includes the text you want.

=INT(A2) & ” hour ” & ROUND(MOD(A2,1)*100,0) & ” min”

Siti June 7, 2012 at 11:31 pm

Thanks Gregory, how ever I still cannot get it..
Let try one more example if I want to calculate overtime (all in HH:mm format)

17:30 until 20:00 = 2:30, I want 2:30 become 2 hours 30 min
if I’m using your formula, its not work because I’m using HH:MM format.

Gregory June 10, 2012 at 12:29 pm

Using the hh:mm time format, and assuming that 2:30 is in cell C7, the formula will be:

=HOUR(C7) & ” hour ” & MINUTE(C7) & ” min”

Siti June 10, 2012 at 5:44 pm

Its work,
thanks Gregory=)

Selvam July 5, 2012 at 5:36 am

Hi,

If example i have Cell A2 = 8:43:30 now i need to add 3:30 and have a result how to do.

Help me how to do.

Gregory July 7, 2012 at 1:39 pm

Select cells B2 and C2, right click the selection and select Format Cells from the pop-up menu. Select the Number tab in the Format Cells dialog box, and under Category click Custom and type hh:mm:ss then click OK. Now these cells show the hours:minutes:seconds.

If 3:30 is Hours:Minutes, then type in 3:30 into cell B2. Then in cell C2 enter the formula =B2+A2 and you’ll get 12:13:30 as your answer.

If 3:30 is Minutes:Seconds, the type in 0:3:30 into cell B2. Then in cell C2 enter the formula =B2+A2 and you’ll get 8:47:00 as your answer.

Timothy August 2, 2012 at 5:19 am

To whom may be able to help,I have a work spreadsheet with detail of the parts I order .
It contains several formulas in hidden columns which tell me how many days since the order was placed and shades cells when the order is recieved etc etc.
Can you tell me of a formula which will highlight a cell in the part number column if the same value is entered say within 7 days?( to prevent me ordering the same thing too soon).
The sheet has a manually entered date for the date ordered and a column with todays date ….a formula subtracts one from the other telling me how many days since it was ordered.
Hope you can help…Tim

Gregory August 14, 2012 at 5:17 pm

You can use conditional formatting to accomplish this task in a couple of steps. Assume Part Number is in column A, Start Date in column B, Today’s Date in column C, and Delta Days in column D. Further assume the headings are in row 1 and the data starts in row 2.

To create conditional formatting for Part Number, select the Part Number date and choose Conditional Formatting (this varies by version of Excel.) You have to enter a formula, so navigate to this within the conditional formatting dialog box (again the navigation is different in each version of Excel.)

The formula you want is for the top row of your selection, in this case row 2. Hence my formula is =D2<8 and then select the formatting you want, then click OK. What this formulas does is look to the number of days and if it's less than 8 (0 to 7) then the condition will be true and the formatting will occur. It's important to open the conditional formatting dialog box and make sure Excel didn't add an absolute reference, like =\$D\$2<8 with the dollar signs. If this happends remove the dollar signs and your all set.

Hope this helps.

Sam August 7, 2012 at 12:23 pm

I want to display a time interval in my excel spread sheet that looks like this
5:00 PM – 5:15 PM

So, that if I type in the time, 5:00 PM, for example what will come out in another cell is
5:00 PM – 5:15 PM

I have tried on my own and I keep getting the time in the decimal format. Please help.

Gregory August 11, 2012 at 8:56 am

Sorry for the delay in responding. Assume you enter 5:00 PM into cell A1. Then in cell B1 you would type in the following formula:

=TEXT(A1,”h:mm AM/PM”) & ” – ” & TEXT(TIME(HOUR(A1),MINUTE(A1)+15,0),”h:mm AM/PM”

When you combine the two times with a hyphen between them, you end up with a Text cell format. Therefore you have to use the TEXT formula that will provide the formatting you specify in the second argument. I had to use the TIME function (with HOUR and MINUTE embedded) so I could add 15 minutes to the first time value, and then use the result as the value for the TEXT function.

matthew August 15, 2012 at 7:11 am

Hi, is there a way in which I can simply type in a time value in one column (example a an event which begins at 9:30AM) and then have it calculate another time in a different column some time earlier (example: 2nd column shows up two hours earlier as 7:30AM). I am basically looking to type in one time and them the other column shows an earlier time (like a work shift which begins some time before an event executes).

Gregory August 19, 2012 at 10:03 am

Assume you have 9:30 AM in cell A2. You can type the following formula in cell B2, which will give you the time 7:30 AM or 2 hours earlier.

=TIME(HOUR(A2)-2,MINUTE(A2),0)

Use the TIME Function, which has three arguments: Hour, Minute, Second. You have to use HOUR and MINUTE Functions to convert the time in cell A2. The last argument for Seconds can be zero (0). You simply subtract 2 inside the first argument for Hours (but not inside the HOURS function). If you wanted to subtract 1 hour and 30 minutes, which is 90 minutes, the formula would be:

=TIME(HOUR(A2),MINUTE(A2)-90,0) which would give you 8:00 AM.

Hope this helps.

Denita August 27, 2012 at 7:21 pm

I have temperature data for every hour of the year for my research. I needed to convert AM/PM time to military time. When trying to format cells or copying into a new cell and format, it always gives me the (1/1/1900 12:45) in the same cell. I have also tried manually typing in the first few and dragging down, but it resets to that format for every 24-hour period. To complete the sorting I need to do, I can’t have that date in the cell. I have no idea how to remove it at this point aside from manually doing it to 8000 cells x 8 field sites. Do you know of a much more practical solution to this problem?

Gregory August 29, 2012 at 6:59 pm

To rid yourself of the “date” component of the date/time serial number, you only need a formula. Assume you have your date/time of 1/1/1900 12:45 in cell A3. I entered the following formula in cell B3:

=TIME(HOUR(A3),MINUTE(A3),SECOND(A3))

and that rids you of the Year portion. You end up with 12:45.

Now you can copy the formula down the column. This can be done simply by selecting the cell with the formula, and double clicking the bottom-right corner, which is called the “fill handle.” Next you can select the entire range with the formulas, then Copy and Paste Special as Values to rid yourself of the formula and leave only the new values.

Hope this helps.

Denita August 29, 2012 at 8:18 pm

You just made my day off so much more productive. Thank you!

Angel September 28, 2012 at 11:02 am

Okay, Maybe I’m just confused. I just want to add hours, as if compiling hours for a time sheet. So, how can I had the hours from 6:00 AM to 6:00 PM, etc. I tried the formatting and I’m pretty sure it’s just something silly that I’m doing. Thank you.

Gregory September 28, 2012 at 11:13 am

If 6:00 AM is in cell A1, and 6:00 PM is in cell B1, then in cell C1 enter the formula =HOUR(B1-A1) which will give you 12.

Angel September 28, 2012 at 11:22 am

Thank you. What if you have an odd number time such as 6:00 AM to 6:45 PM and you want it to read 10.45?

Gregory September 28, 2012 at 11:31 am

Then use =(B1-A1)*24 and you get 12.75 which the difference on time multiplied by 24 hours.

45 min = .75 hours.

Make sure you change the cell formatting to General.

Cindy October 1, 2012 at 9:41 am

I’m trying to organize a teleconference of people who are in different time zones (one hour behind me to 16 hours ahead of me). Is there a formula that will tell me what time it is in someone’s location based on a time and date I enter for my location?

Gregory October 1, 2012 at 9:53 am

Not that I’m aware of. However, I believe timeanddate.com has something like that for meetings across time zones.

Cindy October 1, 2012 at 10:30 am

I actually figured it out by playing around with it a bit more. All cells are custom format m/d/yyy h:mm AM/PM.

Cell A1 = my location date and time
Cell A2… = the cities of my committee members
Cell B2… = difference in hours between me and the members e.g. -1, 0, +14, etc.
Cell C2… = \$A\$1+(B2/24)

You have to put in the – and + signs to calculate the hours properly. It calculates the change in dates if you move into the next day. So all I have to do is change the time and date in Cell A1 whenever I need to schedule a teleconference. Handy!

Gregory October 1, 2012 at 10:49 am

From your earlier comment, I didn’t realize you had all the cites and time differences figured out. Glad to see you’ve put it together, especially with the change in dates. Good stuff.

Cindy October 1, 2012 at 11:25 am

Oh yes, I forget to say that I knew the time differences. It was easy enough to figure out the times in my head, but I didn’t want to change the dates and times every time I have a teleconference. Figuring out the formula makes it easier to try and schedule a time where people don’t have to wake up at 3 a.m. to participate. Thanks!

Siti November 1, 2012 at 4:41 am

Dear,

How to get the sum of hour and minute if i have the following data,

From To total H & M
17:30 20:00 2 hour 30 min
15:30 20:00 4 hour 30 min
14:30 20:00 5 hour 30 min
14:30 20:00 5 hour 30 min
14:30 20:00 5 hour 30 min
14:30 20:00 5 hour 30 min
14:30 20:00 5 hour 30 min
Grand Total ………….. 0:00

I try to click the sum button but grand total will appear “0:00”

Thank you in advance.

Gregory November 2, 2012 at 2:20 pm

Assume you have the start time in range A2:A8, and the end times in the range B2:B8. Assume the duration is in the range C2:C8 and it’s a text field as you have depicted in your comment (2 hour 30 min, etc.)

To sum the difference for all the entries, in cell C9 you can enter the formula =SUMPRODUCT(B2:B8-A2:A8). However this is only half the battle.

Next you have to use a custom time format for cell C9, which is [h]:mm;@. This is just a regular time format, but you have to add the square brackets around the hour [h] so that Excel will summarize the cumulative time. If you leave off the square brackets it you will always get a number of hours between 1 and 24. The total is shown as 34:30 when using square brackets, which is what you are after.

Siti November 4, 2012 at 5:17 pm

Thanks Sir.=)

FIONA November 19, 2012 at 11:15 pm

Hi Gregory
I have a mixed date/time field imported from other software into excel and it imports it as a serial number such as
20121116111903000

We need it to look like dd/mm/yyyy hh:mm:ss AM/PM. We are using version 2003 excel and have tried the custom format but without success. It remains a serial number.

Thanks for your assistance with this frustrating issue.

Gregory November 20, 2012 at 5:18 pm

The number in your example, 20121116111903000, is not a date/time serial number. It’s simply the date and time exported without any demarcation. That is no colons(:) between the time elements and no slashes (/) between the date elements.

The format appears to be YYYYMMDDHHMMSS000 for the export. From left to right you can see that 2012 is the year, 11 is the month, and 16 is the day. The time appears to be 11:19:03. The actual date/time serial number for this would be 41229.47156.

You can separate these components with the LEFT and MID functions, but those functions return a Text value, so you have to wrap either of those functions into the VALUE function. Then each of those different parts need to be used as arguments for either the DATE function, or TIME Function. And finally you have to add those two functions together to get the result you want. Very cumbersome.

You can break this down into parts to make is easier, which is most certainly what has to be done. However, I did this and it took up 9 cells to get the answer. Assuming the number is in cell A2, the following formula would pull out the Year: =VALUE(LEFT(A2,4)) and a formula for the Month would be =VALUE(MID(A2,5,2)) and a formula to pull out the Day is =VALUE(MID(A2,7,2)). Then you can pull out the Date with the formula =TIME(E2,F2,G2) assuming the Year, Month, and Day formulas just mentioned are in cells E2, F2, and G2, respectively.

The best solution is to try and get the export data in a recognizable Date/Time format. It should be a simple matter, considering the hoops you have to go through with Excel. I’ll send you a worksheet that will pull out the date/time with this type of export data, but it’s only a stop-gap measure. You need better export data.

AUS December 11, 2012 at 12:55 am

Dear Gregory,

i’ve crack my head just to think of this calculation. to make it simple, assume that i have this time in “A” Column read as d:hh:mm and “B” Column with business day(note that 1 is consider as 8 hours)

1:22:53 4
0:02:45 2
0:00:02 1
0:00:07 1
0:01:47 2

“A” column is like timer is in 24hrs format as u can see from it counting above whereas “B” Column in business day. i’ll be guessing that i will required to change the “A” column format to business day first before i could minus it.

i just want to get the business day in “B” to minus the time in “A” and get the result in business day.(8 hours)

Gregory December 11, 2012 at 5:13 pm

Using the first row, multiply 4 business days times 8 hours to get 32 business hours. The designation of d:hh:mm for 1:22:53 as 1 day 22 hours and 53 minutes is where the tricky part comes in. Excel recognizes this number as h:mm:ss or 1 hour 22 minutes and 53 seconds, so we have to convert this number to hours.

Assume 1:22:53 is in cell A2, and a formula to convert to hours is =HOUR(A2)*8+MINUTE(A2)+SECOND(A2)/60 which results in 30.833 hours. Subtracting the two numbers 32 – 30.833 and multiplying the result by 8 business hours per day, gives you 3.86 business gives 0.14 business days.

The second row results in 1.66 business days.

Hope this helps. ðŸ™‚

AUS December 11, 2012 at 11:28 pm

dear gregory,

i get the amount 30.833 where i move the decimal places to 4 in “number” format. however, i did not get the amount of 3.86 nor 0.14 and also 1.66.

i put in at C2 this formula to make it simple. =(32-B2)*8 where B2 is place i put the formula =HOUR(A2)*8+MINUTE(A2)+SECOND(A2)/60.

However, i’ve think of other alternatives for this but still screwed up. Please ignore the previous request and this is the new one. Using almost the same table, i would like to minus today date at 8.30 AM and noon 12.30 PM.

1:00:00 12/12/2012 8:30:00 AM
1:22:53 12/12/2012 12:30:00 PM
0:02:45 12/12/2012 12:30:00 PM
0:00:02 12/12/2012 8:30:00 AM
0:01:47 12/12/2012 8:30:00 AM

i was hoping the answer in C2 to be 12/11/2012 8:30:00 AM and so on for the others. oh and still, under column “A” is supposed to be dd:hh:mm.

Nick December 17, 2012 at 6:10 am

I am a dispatcher and I need to be able to enter times in the format (1354) to represent (13:54). I need help formatting my cell to allow this, and then I need to be able to calculate total number of hours (Sometimes my drivers work into the next day, they may start at 13:54, and get off the next day at 4:32). Can you help me?

Nick

Gregory December 28, 2012 at 1:22 pm

Assume Start Time of 1354 in cell A2 and End Time of 432 in cell B2. The formula to show the number of hours and minutes is:

=IF(B4>A4,TIME(INT(B4/100),MOD(B4/100,1)*100,0)-TIME(INT(A4/100),MOD(A4/100,1)*100,0),TIME(INT(B4/100),MOD(B4/100,1)*100,0)+1-TIME(INT(A4/100),MOD(A4/100,1)*100,0))

which gives you 14:38 hours and minutes when the cell is formatted in h:mm time format. You can multiply that number by 24 and change the cell formatting to General to get 14.63 hours.

Hedayat December 18, 2012 at 4:21 am

how to add this data h:mm:ss, looks so simple but it gives wrong result.i want total hours(i.e h:mm:ss format).kindly help.

0:12:23
0:27:22
1:28:31
1:25:46
1:28:41
0:55:38
0:12:42
1:20:21
0:38:57
0:47:5
1:44:24
0:26:33
0:49:32
0:27:57
1:51:54
0:11:06
1:37:56
0:43:28
0:15:53

Gregory December 19, 2012 at 6:58 am

Simply sum the cells in the range that holds the time data, and then make sure the cell format for the summary cell is [h]:mm:ss which will allow the hours to accumulate properly. The square brackets around the hours is the key. You can do this in the Custom tab of the Format Cells dialog box.

Siti December 27, 2012 at 6:12 pm

Hi Gregory,

How we want to convert time in 12 hour format to become 24 hour format.
I try to use custom format but it does not work.

Gregory December 28, 2012 at 1:56 pm

The custom format hh:mm will give you the 24 hour format. Or you can choose Time in the Format Cells dialog box and select a time format that does not have AM or PM, which will give you a 24 hour format.

Gregory February 11, 2011 at 9:38 am

You bring up a good point about calculating time between two different dates. I will write a post about this issue next Monday. For now you can refer to the article I wrote: Calculate Hours Between Two Dates and Times in Excel.

Comments on this entry are closed.

Previous post:

Next post: