Shortcut to : key

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have to enter thousands of time card values (7:45, 15:13 etc).
After a few thousand of these entries my hand is tired - especially when I
have to do a Shift + ; for each time entry. I have a Microsoft Natural
keyboard and cannot figure out how to re-set another key - such as the F12 or
the Calculator key to be the : . Any ideas??? Thanks
 
Have you condidered to apply a custom format?
E.g. a format 00":"00 would insert the colon automatically.

Cheers,

Joerg
 
Thanks for the idea. The data entry was much easier but Excel didn't
recognize them as "Times" so when I calculated the elaspsed time my formula
wouldn't work. So thanks anyway - I thought it was going to work!

Margaret
 
Don't give up easily. Your post just didn't include all restrictions and
conditions, which makes it difficult to suggest a proper solution.
Are you working on your own or someone else's workbook? Are you allowed to
change formats (obviously you are) and formulas? Are you allowed to run
macros? Are you allowed to change your computer configuration? Are you
working on one big worksheet or many small ones?

If I were you I would try to avoid the input of the colon altogether instead
of remapping the keyboard layout.
So why not do the next step and change your formula?
Your present data (input with your tedious Shift + : approach) may look like
this:
A1 B1 C1
8:30 15:45 =B1-A1 => cell C1 shows 7:15 (because Excel
automatically changes the format to data to show correct 7 hours, 15 min
difference)

If in above example you only replace A1 and B1 with the custom format I
proposed, your formula (still formatted as date) will not work.

To make it work you could change your formula to
=TIME((B1-A1)/100,RIGHT(B1-A1,2),0)
Now you can input 830 in A1, 1545 in B1 and your formula will show correctly
7:15 in date format.

There are still other options open, but before we go to plan B, let me know
if above proposal works for you.

Joerg
 
Back
Top