# Building a better mousetrap.

#### DanoQuestion

##### New Member
Hey guys,
I'm brand new here, and I just joined a few minutes ago. I work at a prison work release and we have to sign prisoners in and out of the facility, and sometimes it gets pretty overwhelming. When you work 12 hour graveyard shifts, it's kind of hard to convert, calculate and subtract military time in your head.
So I set out to make a spreadsheet that would make things much easier for us at night.
I learned that Excel won't subtract military time without out you using a colon. :

So, for me, that was a big problem. Making a colon is two extra key strokes, and I thought that should be eliminated. I scoured the Internet and I came up with a formula that would insert the ":" for you.
For example: If I enter 1846......my formula will make it 18:46.

This is the formula:
=IF(ISBLANK(B3),"",TIME(INT(B3/100),MOD(B3,100),0))

That formula would be entered in cell B4 (I forgot to draw that box in my example)
once the new values of B3 and C3 have been changed with the added colon, then cells B4 & C4 could be added or subtracted, with the results ending up in cell D4.

I'll show you a screen capture of what I came up with.

But since we now have a touchscreen laptop, I'd really like to be able to bump it up a notch.

This is what I had in mind.
Would it be possible to create a virtual keypad on the side, so we didn't have to use the awkward laptop's keyboard? The only operators I'd need would be numbers 1-0 and an "enter" button.

Any help would be greatly appreciated, and if my post doesn't make sense, I can add any missing information you might need.

I believe we use Excel 2013 at work.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### DanoQuestion

##### New Member
Thanks. I'll try and embed that code into an Excel worksheet. But that doesn't look like it will subtract military time. I'll tinker with it.

#### Logit

##### Well-known Member
.
I think the first challenge is to determine if the touch screen will work with any calculator.

#### DanoQuestion

##### New Member
OK, I'll do that tonight when I get to work. I don't have Excel at home. I'm about 90% sure the touch screen will work, because I've put buttons on spreadsheets before and assigned them macros, and the touch screen buttons work great. But I'll let you know.