Please help with Time subtracting

[Ted007]

I've tried evrything, this is what I need to do:

A1 = time in
B1 = time out
C1 = difference in hours and minutes

Now here is the hard part:

I want to enter data for A1 and B1 eg. 1234 and have it display 12:34
but not have to use th ":" separator.

I have C1 currently formatted for hh:mm with a formula of B1-A1
But I have to constanly hit "shift" ":" when entering data in cells A1,
B1. I looked all over the web to no avail, I thought this was a really
simple and common excel situation. Any help will be greatly
appreciated.
Thanks

 

[Harald Staff]

Hi

Problem is that 1 is a day. So one hour is 1/24 and 12:34 slightly more than 0.5. Format a
time cell as a decimal number to see this happen.

Excel can't take 1234 -more than a thousand, or almost 4 years worth of time- and convert
it into a 0.5 just like that. You need a macro for those things. Here's one:
http://www.cpearson.com/excel/DateTimeEntry.htm

 

[Fabian]

Ted007 hu kiteb:

I've tried evrything, this is what I need to do:

A1 = time in
B1 = time out
C1 = difference in hours and minutes

Now here is the hard part:

I want to enter data for A1 and B1 eg. 1234 and have it display 12:34
but not have to use th ":" separator.

D1=sum(rounddown(a1/100,0)/24,(a1-rounddown(a1/100,0)*100)/24/60)
E1=sum(rounddown(b1/100,0)/24,(b1-rounddown(a1/100,0)*100)/24/60)
C1=E1-D1

Or, if you're masochistic, skup the D and E columns, and use:

C1=sum(rounddown(b1/100,0)/24,(b1-rounddown(a1/100,0)*100)/24/60)-sum(ro
unddown(a1/100,0)/24,(a1-rounddown(a1/100,0)*100)/24/60)

You'll need to enter this in 24-hour clock style. To see columns A and B
in clock style, use the following line as a custom format:

00":"00