On 2002-09-01 17:06, Gichiguy wrote:
I'm learning but I'm Stuck.
Time calculation :
<pre>
1st cell 2nd cell
15:30 1:45 (10:25)
</pre>
It is 10.25 hrs from 3:30pm to 1:45 am
Hi - welcome to the board.
You've run up against some of the quirks of how excel stores dates & times (so - first step = read up on the help file for 'how excel stores dates & times' !).
Times are:
1) stored as decimal fractions of days, so 12 hours = 0.5, one hour = 0.04 etc.
2) Date values are stored as numbers from 01 Jan1900 - 021 Jan 900 = 1, 02 Jan 1900 = 2 etc
3) times entered without a date are assumed by excel to have a date value of 0.
so,
4) entering 15:30 will give you a date-time value of 0/01/1900 15:30:00; 01:45 will give you a date-time value of 0/01/1900 01:45:00 - hence your calculation falling over.
To fix this, you need to get excel to recognise that the second time is after the first. Use a formula of the form:
=IF(A1>B1,B1+1-A1,B1-A1)
where a1 is start time & b1 is end.
You will also need to format your cell as [hh]:mm, which is the format for elapsed time calculations
also, you could check out
http://www.mvps.org/dmcritchie/excel/datetime.htm
which has loads of info re dates & times in excel.
Hope this helps,
Paddy
