calculating and displaying time

M

matmich

i need to enter some time info. and calculate my findings. For som
reason i am having trouble. how do i format my cell to display time??

EX. I enter 655 -- I want to see 6:55


second how can i calc time
EX start 6:55
end 7:10
ANS 1
 
R

Ron Rosenfeld

i need to enter some time info. and calculate my findings. For some
reason i am having trouble. how do i format my cell to display time??

EX. I enter 655 -- I want to see 6:55


second how can i calc time
EX start 6:55
end 7:10
ANS 15
Click to expand...

1. Enter 6:55
2. Enter 655, then, in an adjacent column, enter the formula:
=TIME(INT(A1/100),MOD(A1,100),) to convert it to Excel time
3. Write a VBA routine that does the conversion automatically after you enter
the number. Post back if that is what you require.

If you enter 6:55 in A1, and 7:10 in A2, then =A2-A1 will give you 0:15.

If you want 15, then =(A2-A1)*1440 and format as General.

Excel stores time as a fraction of a day. So 6:55 is stored as 6/24 + 55/1440
(1440=minutes in a day)

The result of 0:15 is stored as 15/1440, so to see 15, multiply by 1440


--ron
 
G

Guest

Try this.
When you enter time, enter with colon separator. Excel
will recognize it as time.
If your time points pass midnight, enter date as well
(e.g. 6/7/04 6:55). In the calculation cell, use time
format 37:30:55; this gives deltas in h:m:s

Hope this helps.
 
Top