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.
4 Answers
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.
4Mestika,
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
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].
I use this formula:
=IF((OR(B1="",A1="")),0,IF((B1<A1),((B1-A1)*24)+24,(B1-A1)*24))
B=TimeEnd
A=TimeStartI 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.
