# excel, office, formula, calulate

I have an Excel Document that has the following columns:
A11 = Date In  (Date Format)
B11 = Time In      (Time Format)
C11 = Date Out       (Date Format)
D11 = Time Out (Time Format)

We need to calculate the amount of hours and minutes between the two date and time period.
How can this be done?

Screenshot of the spreadsheet setup attached.
###### Who is Participating?

freelancerCommented:
this will work; but it has no exception handling

=(C11-A11)+(D11-B11)

e.g.
``````date in      time in      date out     time out   hours
1/02/2013      9:16      1/02/2013      17:23      8:07
``````
if you think that isn't sufficient try:
``````=DATE(YEAR(A11),MONTH(A11),DAY(C11))-DATE(YEAR(C11),MONTH(C11),DAY(C11))+(TIME(HOUR(D11),MINUTE(D11),SECOND(D11))-TIME(HOUR(B11),MINUTE(B11),SECOND(B11)))
``````

If the number of days isn't multiplied by 24, but (for example) a "day" is 8 hours, then:

=((C11-A11)*8)+(D11-B11)

or the more comprehensive alternative:

``````=(DATE(YEAR(A11),MONTH(A11),DAY(C11))-DATE(YEAR(C11),MONTH(C11),DAY(C11))*8)+(TIME(HOUR(D11),MINUTE(D11),SECOND(D11))-TIME(HOUR(B11),MINUTE(B11),SECOND(B11)))
``````
and the "8" (hours per day) could be a cell reference of course.

if there are any standard deductions (e.g. for lunch) then that could be added into the formula also.
0

Commented:
Hi, bax2000.

The formula is...
=A13+A14-A11-A12
..and you need to format the cell as follows...
[h]:mm
...(the square brackets tell Excel to display Hour values greater than 24.)

Regards,
Brian.
0

Systems EngineerAuthor Commented:
All Working as needed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.