Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. I agreed to these rules
Join Date
12th February 2006
Posts
11

## Time Calculation Formula

Hello,
I'm a total newbie to this forum and not really more then a beginner in excel use.

I have attached a time card that needs a formula to calculate the time for the high lighted areas.
In+out-in+out= total hours.

To be honest i have not been able to take the time to absorb how hours and dates work in Excel so i'm here for any help i can get.

File is attached.

Trix

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Time Calculation Formula

Assuming all the times are in the same day (I think you meant for your last Out time to be 3:45 PM not the AM that is in your attachment), thyen you can use:

=Q1-N1-(P1-O1)

Format the cell as Number > Time > 13:30 o eliminate the AM/PM display.

3. ## Re: Time Calculation Formula

Addendum: Also have a look at the Dates and Times help link found here:

http://www.ozgrid.com/Excel/
Last edited by thomach; May 11th, 2007 at 23:53.

4. Have VBA, will travel
Join Date
24th December 2004
Location
Nanaimo, Vancouver Island, British Columbia, Canada
Posts
2,464

## Re: Time Calculation Formula

Hi Trixboomer,

Excel treats a day as a number = 1. ie 1 = 1 whole day. so any part of a day is a fraction, actually decimal. midnight to noon is 12 hours to us humans but it's 1/2 a day to Excel ie .5 of a day. so far so good.

What about 6AM? to us that's early! to Excel that is only .25 of a day. noon is .5 of a day, 6 pm is .75 of a day.

So forget base 24 or Am/Pm base 12.

Just:

(Latest time - earlier time)*24 = hours elapsed

You must subtract the lesser time from the greater and multiply by 24 to get number of hours. If it spans midnight then add 1. the concept is simple the formula is hell <g>.

The formula for spanning midnight is:

=(F4-C4+(F4<C4))*24

Found here (a classic)

http://www.cpearson.com/excel/overtime.htm

5. ## Re: Time Calculation Formula

Originally Posted by rbrhodes
If it spans midnight then add 1
Or just format as [h]:mm to see total hours.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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