Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating Time (2002 SP3)

    <P ID="edit" class=small>(Edited by Kel on 02-Nov-04 09:35. )</P>I am trying to setup a formula for a timesheet, however I am unsure of what functions I might use to make this happen. My table looks something like this:

    <table border=1><td>IN</td><td>OUT</td><td>IN</td><td>OUT</td><td>IN</td><td>OUT</td><td>HOURS</td><td>8:00</td><td>10:00</td><td>10:30</td><td>12:00</td><td>12:30</td><td>5:00</td><td>??</td></table>

    I'd love for this thing to calculate out the amount of time I was actually here automatically. Any ideas? Note that hours may vary widely, I can arrive as early as 4:00am, and/or leave as late as 9:00pm.

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Time (2002 SP3)

    You will need to specify AM and PM in the time, or use 24 hour time, and, if the times will ever run from one day to the next, you'll also need the date, but you should still format the cells as "h:mm AM/PM" or "h:mm" for 24 hour time.

    Kel, if I copy what you posted into Excel, it tends to assume everything is AM, but I expect the 5:00 is PM? Once you resolve that, the general fromula for elapsed time will be
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>1</td><td>IN</td><td>OUT</td><td>IN</td><td>OUT</td><td>IN</td><td>OUT</td><td>HOURS</td><td align=center>2</td><td align=right>8:00 AM</td><td align=right>10:00 AM</td><td align=right>10:30 AM</td><td align=right>12:00 PM</td><td align=right>12:30 PM</td><td align=right>5:00 PM</td><td>=B2+D2+F2-A2-C2-E2</td></table>
    All formatted as "h:mm AM/PM" except G2 which is "h:mm".
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating Time (2002 SP3)

    I suggest formatting G2 to "[h]:mm" to allow for elapsed times (> 24 hour) rather than just time of day.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Calculating Time (2002 SP3)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> To rephrase Peewee Herman, "I meant to say that!"
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Calculating Time (2002 SP3)

    It was probably just a typo <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •