close

Privacy guaranteed - Your email is not shared with anyone.

Time calculator (excel?)

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

  1. MStarmer

    MStarmer In Jail...

    Joined:
    Feb 26, 2000
    Messages:
    1,488
    Likes Received:
    7
    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

    aspartz

    Joined:
    Oct 19, 2000
    Messages:
    3,281
    Likes Received:
    133
    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

    grokdesigns

    Joined:
    Dec 23, 2004
    Messages:
    507
    Likes Received:
    0
    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. MStarmer

    MStarmer In Jail...

    Joined:
    Feb 26, 2000
    Messages:
    1,488
    Likes Received:
    7
    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
     
  5. grokdesigns

    grokdesigns

    Joined:
    Dec 23, 2004
    Messages:
    507
    Likes Received:
    0
  6. MStarmer

    MStarmer In Jail...

    Joined:
    Feb 26, 2000
    Messages:
    1,488
    Likes Received:
    7
    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

    grokdesigns

    Joined:
    Dec 23, 2004
    Messages:
    507
    Likes Received:
    0
    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. MStarmer

    MStarmer In Jail...

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

    seanmac45 CLM

    Joined:
    Apr 13, 2000
    Messages:
    7,523
    Likes Received:
    6,954
    Location:
    Brooklyn, NY