#### DanoQuestion

##### New Member

- Joined
- Oct 15, 2019

- Messages
- 3

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.

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.