Input time and have colon automatically appear

0

My question: how can i sign on on C3 inputing only 2100 and have the colon automatically inputted? I want to see 21:00 on C3 after input. Column C is Sign on time starting on row 3, the following formula calculates time on position even going past 24:00. [=IF(C4<C3,C4+24-C3,C4-C3)]  I have D column automatically calculating time using the above formula which works perfectly IF I input the ":" but, I'm lazy and I want that colon inputted for me.  Thank you. I'm going slightly beserk because I think I'm missing something too easy....

Answer
Discuss

Answers

0

You can use two columns and input a formula like this in one of them:

=LEFT(A1,2)&":"&RIGHT(A1,2)

The problem then is that the "time" is a formula output and not a very easy-to-manipulate 'time'.

You could also create a macro to update the input into the desired time format but, honestly, that's a lot of work for avoiding hitting the colon key.

Time can be a real pain in Excel and I wouldn't do anything to make it more complex for yourself!

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login