time formula

V

vdmbqb

I need to enter time in hours and minutes and have the result equal time in
hrs and 100th minutes. ie; 1650-800=8.83 or 1045-800=2.75

any help anyone
 
B

BriSwy

Hi Bob.

Assuming that you have 2:50AM entered in cell A1, use this formula:

=(HOUR(A1)*60)+(MINUTE(A1)/60)

Make sure the cell that you are entering the formula in is formatted as
numeric with 2 decimal places.
 
S

Sandy Mann

vdmbqb said:
I need to enter time in hours and minutes
Click to expand...

Assuming that by that you mean you are entering the times as XL times ie
hh:mm, then:

XL stores dates as the number of days since the start *date* of January 00
1900, ie day 1 is January 1 1900. Times are just fractions of days so being
as 1 hour is 1/24th of a day, multiplying a *time* by 24 will result in
decimal hours. Therefore with 16:50 in A1 and 8:00 in A2,

=(A1-A2)*24 results in 8.833333

and with 10:45 in A1 it results in 2.75

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
B

BriSwy

Take 2. Please ignore my first solution. Here is what you will need:

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

This will return 2.83 (also the equivalent of 2 hours and 50 minutes).

Hope this helps and sorry for the mixup on Take 1!
 
Top