Time problem.... please help.

Posted by Regan on April 11, 2001 6:46 AM

I'm having troubles w/ a time formula...
What I need is as follows

A1 will be the start time,let's say 13:00
B1 will be the finish time, 17:45
I need a formula for C1 that will show the amount of time worked BUT it has to be broken down into the decimal form. (C1, using the above times, would show 4.75 )

I really need help !!!
Thanks

Posted by Mark W. on April 11, 2001 7:13 AM

Enter the formula, =(A2-A1)*24, into cell C1 and
format as 0.00.

Posted by David P on April 11, 2001 7:17 AM

Regan,

The formula

=(HOUR(B1)+MINUTE(B1)/60)-(HOUR(A1)+MINUTE(A1)/60)

should work. It converts the minutes into fractions of an hour, then takes one time from the other.

Alternatively you could use

=(B1-A1)*24

This takes one time from another, which gives the result in a fraction of a day, then multiplies it by 24 to convert it into hours.

Hope this helps.

David

Posted by Paul on April 11, 2001 7:18 AM

Input the formula "B1-A1" into C1.

Then right click on C1,
go to format cells, and change the "category" to Number format.

This should work

Posted by Regan on April 11, 2001 7:29 AM

Thanks Mark but when I enter that in I either get ######## in the cell.

Posted by Big bob on April 11, 2001 7:42 AM

The hash signs "#" either mean the cell width is too narrow or that the time difference is negative so try adjusting the width of the column and if that does not work swap over the two times

Posted by Regan on April 11, 2001 7:42 AM

Thanks David but when I use the first formula you gave me, the end result in C1 ends up being 18:00 when I need it to say 4.75 , I'm not sure if I've messed it up somewhere.
The second formula only gives me ########'s

Any idea ?

Posted by Mark W. on April 11, 2001 7:53 AM

Sorry, I used the wrong cell references...

Enter the formula, =(B1-A1)*24, into cell C1 and
format as 0.00.

Posted by Mark W. on April 11, 2001 7:59 AM

Paul, =B1-A1, will produce a fractional result,
0.1979166..., that is in terms of days rather
than the requested hours.

Posted by Regan on April 11, 2001 11:01 AM

Perfect Mark !!!!! Thanks a million.

Mark but when I enter that in I either get ######## in the cell.