Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I’m working on a very simple time sheet for my work in Excel 2007, but I have run into trouble about calculate the hours and minutes between two time periods.

I have a field: timestart which could be for example: 08:30

Then I have a field timestop which could be for example: 12:30

I can easy calculate the result myself which is 4 hours but how do I create a “total” table all the way down the cell that calculates the hours spend on each entry?

I’ve tried to play around with the time settings but it just give me wrong numbers each time.

share|improve this question
add comment

4 Answers

up vote 2 down vote accepted

If your timestart values are in Column A, and your timestop values are in Column B, then in the column of your choice put something like:

=ABS(HOUR(A1)-HOUR(B1))

Then grab that formula and drag it down the sheet.

share|improve this answer
    
Hi Lance, your formular works but I have one question I didn't though of when posting my question. I also have some time periods where it is only a half an hour, such as starttime: 12:00 and endtime: 12:30 but then the HOUR can of cause not calculate. Is there some ways to work around that problem? –  Mestika Jun 1 '10 at 16:57
    
@Mestika, what result do you want to see for those values? Do you want fractional hours, or always whole numbers? How do you want to round partial hours? –  Lance Roberts Jun 1 '10 at 17:03
    
I'm a bit new to the excel date/time formation but I was thinking that either it could say: 4,5 hour if it is from 12:00* to *16:30 or I don't know, maybe in minutes and then calculated into hh:mm formation. The thing I'm after is, if someone has worked from 12:00 and to 16:35* he has totally worked for 4 hours and 35 minutes in that time period. –  Mestika Jun 1 '10 at 17:43
    
I actually got it to work. I used JDBs suggestion at the top and when formatting my cells in a hh:mm format and calculated: =SUM(B1-A1) it gives the correct result. And there by I can also create a total monthly work time. –  Mestika Jun 1 '10 at 17:47
add comment

Mestika,

It sounds like you already know how to calculate the values - it sounds like you need help formatting the results. Typically Excel uses a value of 1 to represent a day. (e.g. 6 hours would be 0.25) You should be able to subtract two times (as others have suggested) and then multiply by 24 to get hours. Summing a column of those values will get you what you need.

Josh

share|improve this answer
add comment

If 8:30 is on cell A1 and 12:30 is in cell B1, then entering the formula =B1-A1 in C1 will give you the right answer, and (I've just tested in Excel 2010) should format it crooectly.

You can then SUM the values in column C to give a total.

However there can be problems that can bite you, basically because Excel's time and data functions work together. Dates are whole numbers, 1 being 1 January 1900, 2 being 2 January 1900 etc. Time is a part of a day,so 1.5 is noon on 1 January 1900, 2.25 is 6 am on 2 January 1900.

The point of this is that when you start adding time, it starts to roll over into days, so if you have a total of 25:00 hours, you are likely to see a total of 1:00 hours. To stop Excel doing this you need to format the cell with square brackets around the hours element, [hh].

share|improve this answer
add comment

I use this formula:

=IF((OR(B1="",A1="")),0,IF((B1<A1),((B1-A1)*24)+24,(B1-A1)*24))

B=TimeEnd
A=TimeStart

I know it seems a complicated string but it works and will give you not just whole values but at 1/8 min intervals. I have them separated to account for lunch break then I can just add using a =sum() equation.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.