Cell time formatting and calculation

[Darrin Smith]

I am trying to set up a sheet with individual cells containing "start time" and "stop time" in 24 hour format and have another cell calculate the elapsed time and give value in decimal hours.

eg.
12:45 to 12:53 (8 minutes elapsed) to display as 0.1

or
14:27 to 15:19 (52 minutes elapsed) to display as 0.9

Each 6 minutes = 0.1 hrs. ( 1 and 2 minutes over 6 should round down.... 3, 4 and 5 minutes past 6 should round up)

Any ideas?

 

[Brian from Maui]

Darrin,

Try mulitplying by 24 and format the cell that houses the formula as a number to 1 decimal place.

 

[Darrin Smith]

Hey Brian

You may be a few steps ahead of me.... I'm very new to Excel.
I've not yet been able to format the "start & stop " time cells to a time format of any kind. Also, I imagine there would have to be somewhere in the formula, a referance to 60 being 1.0 or a whole...... ?

 

[Brian from Maui]

Hey Brian

You may be a few steps ahead of me.... I'm very new to Excel.
I've not yet been able to format the "start & stop " time cells to a time format of any kind. Also, I imagine there would have to be somewhere in the formula, a referance to 60 being 1.0 or a whole...... ?

Darrin,

In a cell, say A1 enter 0:06. In B1 enter this formula,

=A1*24

Now activate B1 and go to Format/Cells/Number tab and select Number, Decimal Places, select 1

 

[Darrin Smith]

Brian

It's gonna test you.......


F10 is my "start time" ( will be manually entered in 24 hour style)
G10 is my "stop time" ( will be manually entered in 24 hour style)
H10 is my "elapsed time" ( I want it to calculate for me, and display as decimal hours)

I entered 0:06 into F10, then selected G10 and entered the formula =F10*24 ( G10 then changes to 2:24 )
I selected again G10 and did ... Format/Cells/Number tab and select Number, Decimal Places, select 1 ( G10 then changes to 0.1 ) ?

I'm at a loss to see how this would work??

But keen to learn how......

 

[Brian from Maui]

Darrin,

Here's a sample. The formula in C4 takes into account going over to the next day. Also column C is formatted as a Number, one decimal place.

Book1
	A	B	C	D
1	Start Time	End Time	Elasped Time
2	13:00	13:06	0.1
3	13:00	13:10	0.2
4	23:10	0:10	1
Sheet1

 

[Darrin Smith]

Brian

Thanks heaps Brian...... now I get it.

Rgds
Darrin