Results 1 to 7 of 7

20031108, 01:23 #1
 Join Date
 Jan 2001
 Location
 Swift Current, Sask. Canada, Saskatchewan, Canada
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Create time difference formula (Excel 2000)
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:008:30 gives me 3.7 instead of 3.5. Can someone help me?

20031108, 09:43 #2
 Join Date
 Nov 2003
 Location
 Germany
 Posts
 75
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Create time difference formula (Excel 2000)
Hi S Peters,
Format A1, A2 and A3 as Time.
Type in A1: 12:00
Type in A2: 8:30
Type in A3: +A1A2
A3 will show you 3:30

20031108, 17:59 #3
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Create time difference formula (Excel 2000)
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 =A2A1 (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 =(A2A1)*24 and format the cell as a number with two decimal places. This is because Excel stores times as the fraction of a day.Legare Coleman

20031110, 15:31 #4
 Join Date
 Jan 2001
 Location
 Swift Current, Sask. Canada, Saskatchewan, Canada
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Create time difference formula (Excel 2000)
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.

20031110, 21:13 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Create time difference formula (Excel 2000)
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.

20031111, 01:46 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Create time difference formula (Excel 2000)
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?
Legare Coleman

20031111, 02:00 #7
 Join Date
 Jan 2001
 Location
 Swift Current, Sask. Canada, Saskatchewan, Canada
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Create time difference formula (Excel 2000)
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.