Excel Formula for calculating time in round numbers with hours text

Question

I'm trying to figure out a way to create an excel spreadsheet that will allow me to keep track of how many hours I've earned per each paid holiday my company offers, then keep track of how many hours I've used and what's remaining. But I'm unsure how to calculate this properly.

I could easily do the math my self as it's a simple lay out, but I'm trying to find a way that will just let me enter the numbers for earned and used and walk away from anything else.

What I'm trying to do is the follow:

Have multiple sections. In the first section it'll be my holidays. So in like Column A, working down I'd have New Years, Memorial Day, July 4th, etc. Column B working down would be time earned. But this would be labeled in each cell as "8 Hrs" or "4 hrs". Column C would be time used in the same format "4 hrs" "8 hrs". Then Column D would simple be hours remaining.

Now in the second section I'd have holiday hours earned, which is 2 weeks. Not too sure how to lay it out, and then I still have my sick days, but not sure if I should include that in section 2 or not with the vacation time.

I'd like a way to be able to simply Calculate B2 - C2 = D2. So 8 Hrs - 4 Hrs = 4 Hrs (to show 4 Hrs remaining for each line item.)

Problem is I'm unsure how to calculate remaining time simply because of the Hrs suffix. And with that I also can't calculate total time still remaining, both for holiday hours earn and vacation/sick hours.

Yes, it's easier for me to track it as 8 Hrs rather than 1 day, etc.

Any advice on how to formulate this. Or if anyone knows of a premade template that fits this type of scenario, that I could then just take and integrate into my own spreadsheet.

Sorry if this is confusing in any way.

Also, As I'm no excel wizard, unsure if this is relevant or not, but I'm using Excel 2016, as part of the Office suite.

Answer 1

Another option is to just leave the number as it is and label the column accordingly. Do you really need to see "hrs" in every cell when you know you are tracking hours?

Answer 2

Use a custom number format of,

[>1]0 \H\r\s;[=1]0 \H\r_);0 \H\r\s;[Red]@

... and treat all hours as integers.