Create time difference formula (Excel 2000)

Topic

New Reply

I am a novice to Excel. I am making a time sheet. I have been trying to make a formula to subtract start time from stop time, but it always gives me a total based on 100 instead of 60. 12:00-8:30 gives me 3.7 instead of 3.5. Can someone help me?

Reply | Quote

Replies

Hi S Peters,

Format A1, A2 and A3 as Time.
Type in A1: 12:00
Type in A2: 8:30
Type in A3: +A1-A2

A3 will show you 3:30

Reply | Quote

If you are getting 3.7 when you do that subtraction, then what you have in those cells are the numbers 12.0 and 8.3, not the times 12:00 and 8:30. What you need to do is enter the times exactly as you typed them in your message with the colon between the hour and the minute and a two digit minute. It looks like the times were entered with decimal points, not a colon between the hour and the minute.

Next, how you do the subtraction and how you format that cell depends on how you want the result to be displayed. If you want the result to display as 3:30 (three hours and thirty minutes), then enter the formula =A2-A1 (if the start time is in A1 and the end time is in A2) and format the cell with the custom format [h]:mm with the brackets around the h. That format is elapsed time and will allow times greater than 24 hours to display correctly.

If you want the result to display as 3.50, then enter the formula =(A2-A1)*24 and format the cell as a number with two decimal places. This is because Excel stores times as the fraction of a day.

Reply | Quote

I have been playing with this formula using the .50 hours instead of the 30 minutes, and works great. The only thing is if I use 09:15 or 09:45, the answer does not come out right. Maybe I will have to stick to half hours. Thanks for the help.

Reply | Quote

As Legare wrote, you *must* enter the two times with colons between hours and minutes: 9:15, not 9.15. If you enter times as 9.15, you’ll have to do complicated arithmetic to get the difference between times right. If you enter them as 9:15, Excel will do most of the work for you. See attached workbook.

Reply | Quote

As Legare wrote, you *must* enter the two times with colons between hours and minutes: 9:15, not 9.15. If you enter times as 9.15, you’ll have to do complicated arithmetic to get the difference between times right. If you enter them as 9:15, Excel will do most of the work for you. See attached workbook.

Reply | Quote

If you are entering the times like you typed in your message (with the colon between the hour and the minute), there should be no problem. Can you attach a workbook that shows the problem you are having?

Reply | Quote

If you are entering the times like you typed in your message (with the colon between the hour and the minute), there should be no problem. Can you attach a workbook that shows the problem you are having?

Reply | Quote

I have been playing with this formula using the .50 hours instead of the 30 minutes, and works great. The only thing is if I use 09:15 or 09:45, the answer does not come out right. Maybe I will have to stick to half hours. Thanks for the help.

Reply | Quote

If you are getting 3.7 when you do that subtraction, then what you have in those cells are the numbers 12.0 and 8.3, not the times 12:00 and 8:30. What you need to do is enter the times exactly as you typed them in your message with the colon between the hour and the minute and a two digit minute. It looks like the times were entered with decimal points, not a colon between the hour and the minute.

Next, how you do the subtraction and how you format that cell depends on how you want the result to be displayed. If you want the result to display as 3:30 (three hours and thirty minutes), then enter the formula =A2-A1 (if the start time is in A1 and the end time is in A2) and format the cell with the custom format [h]:mm with the brackets around the h. That format is elapsed time and will allow times greater than 24 hours to display correctly.

If you want the result to display as 3.50, then enter the formula =(A2-A1)*24 and format the cell as a number with two decimal places. This is because Excel stores times as the fraction of a day.

Reply | Quote

Thanks so much for all the help people. I was having some troubles with all these suggestions until I found the right formatting for the particular cells. Was also having trouble converting the accumulated times into earnings until I tried using the *24 thing with the right formatting in the cells. Again Thank You all. You are fantastic.

Reply | Quote