Building a better mousetrap.

DanoQuestion

New Member
Joined
Oct 15, 2019
Messages
3
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.
3e8bLwA.png


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.
KaupqU0.png
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.
 
Upvote 0
.
I think the first challenge is to determine if the touch screen will work with any calculator.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top