# Thread: Calculating Time (2002 SP3)

1. ## 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!

2. ## 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".

3. ## 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. ## 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!"

5. ## 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
•