close

Privacy guaranteed - Your email is not shared with anyone.

# Time calculator (excel?)

Discussion in 'Tech Talk' started by MStarmer, Mar 17, 2006.

1. ### MStarmerIn Jail...

Messages:
1,488
8
Joined:
Feb 26, 2000
Location:
Washington State, Pacific Northwest
As part of my job now I have to ensure that some of our residents don't exceed our limit of 65hours out of our facility.

We basically write on a weekly calendar sheet from sunday to saturday when they leave and return (in military time format).

Is there an easy formula in excel or a webpage that allows me to calculate the difference daily and total for the week?

I'm not much of a computer guru but I don't want to calculate these by hand if needed.

I just want something quick and easy that I can blaze thru hopefully entering their leave time like 1530 and their return time like 2243 and getting a diff and having it totaled in hours/min.

Edited to correct my horrible in the middle of the night spelling!

2. ### aspartz

Messages:
3,281
133
Joined:
Oct 19, 2000
Location:
Sandstone, MN 55072
Excel is good at time calculations. simply subtract the first cell (the 1st time) from the second. The result is, IIRC, a number of minutes.

The only PITA is entering the times -- excel requires a colon on 24 hr times. Another complication hits if the times cross midnight. fixing this is a simple matter of math, but can be beyond a complete newbie.

ARS

3. ### grokdesigns

Messages:
507
0
Joined:
Dec 23, 2004
http://www.grokdesigns.com/Calculate_Hours.xls

Here is a spreadsheet I threw together for you. It calculates total hours for the day and total hours for the week. It also uses conditional color coding (ex. green means well under the limit, yellow means nearing the limit, and red means over the limit). Hope you find it useful!

<strike>P.S. As was mentioned, if any of your people come back after midnight, you will have problems with the formulas. I didn't program for that, but it's not hard to do.</strike>

EDIT: I updated the spreadsheet to handle overnight times!

4. ### MStarmerIn Jail...

Messages:
1,488
8
Joined:
Feb 26, 2000
Location:
Washington State, Pacific Northwest
Thanks guys, I knew it would be easy for someone! I did try making a quick formula but kept getting errors? But when mentioned entering time with a colon did work.

Thanks again!! ;f ;f

Messages:
507
0
Joined:
Dec 23, 2004
6. ### MStarmerIn Jail...

Messages:
1,488
8
Joined:
Feb 26, 2000
Location:
Washington State, Pacific Northwest
Threw together? ;g Wow I wish I could throw together stuff like that. One of the other officers saw it and was frustrated because he's been trying to get the military time thing to work on one if his projects for months, and I pop open my e-mail and say hey look at this!!

I used it tonight for a half a dozen guys that I thought were getting close for the week. It worked like a charm and was a breeze to use!

Excel is a great program but I think the main problem for most of us is that it just does SO MUCH it's impossible to find the simple things that you're trying to do!

Thanks again this was a very handy little tool.

7. ### grokdesigns

Messages:
507
0
Joined:
Dec 23, 2004
You hit the nail on the head, Excel can do so much more than most people think. Coupled with support for Visual Basic, the possibilities are endless. Anyway, glad you found it useful, feel free to pass it on to anybody else that may find it handy.

-Chris

8. ### MStarmerIn Jail...

Messages:
1,488
8
Joined:
Feb 26, 2000
Location:
Washington State, Pacific Northwest
Thanks again, I hope it wasn't too much trouble!! It rocks ~2!

Messages:
8,157