5

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.

Improve this question

4 Answers 4

Reset to default
2

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.

Improve this answer
4
  • 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, 2010 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, 2010 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, 2010 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, 2010 at 17:47
5

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

Improve this answer
3

If 8:30 is in 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 correctly.

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 date 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].

Improve this answer
0

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.

Improve this answer
1
  • What do you mean, “will give you not just whole values but at 1/8 min intervals”? This will give you resolution well below the microsecond level if you format the cells to show enough precision.
    – Scott - Слава Україні
    Jul 19, 2014 at 1:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .