• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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.
2 Solutions
Hi, bax2000.

The formula is...
..and you need to format the cell as follows...
...(the square brackets tell Excel to display Hour values greater than 24.)

this will work; but it has no exception handling


date in      time in      date out     time out   hours
1/02/2013      9:16      1/02/2013      17:23      8:07

Open in new window

if you think that isn't sufficient try:

Open in new window

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


or the more comprehensive alternative:


Open in new window

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.
bax2000Author Commented:
All Working as needed.
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now