The situation.
I have an excel spreadsheet I use to track missed hours. I type in the time the person leaves and returns in 24 hour time, in numerical format (IE 0600 and 0740 for 6:00 AM through 7:40 AM).
I then manually figure out how long they were gone for. When I do this I round it down to the nearest 15 minute marker (IE 0600-0740 is 1 hour and 40 minutes, I round it down to 1 hour and 30 minutes).
Then I convert that into a simple decimal to identify the number of hours (IE 1 hour and 30 minutes is 1.5 hours).
The problem.
Right now all the math is done manually. I am trying to automate this. I've attached a screenshot of my spreadsheet to demonstrate what it looks like.
Excel demo.jpg
Basically, column F is the out time. Column G is the in time. Column H is the amount of missed hours. I'm trying to make it so I type in the times in Columns F and G using numerical format (0600) and it automatically tells me the number of hours missed in decimal form, rounded down to the nearest 15 minutes (1.25, 1.5. 1.75. 2.0, etc).
Macros don't work due to user permissions issues. Formulas are fair game. Any ideas?
Bookmarks