Excel and time calculations

Drukk · 23 Sep 2007 at 20:58

Hi, Im trying to write an excel worksheet to record my working times. Im slowly learning the basics but one thing im really not sure of is how to make a formula to do time calculations.

What Im trying to do is enter a start and end time in 24 hour clock and then have excel calculate the number of hours worked and display as a decimal.

e.g. 1400 start, 1945 finish would give 5.75.

Anyone have an idea how to do this?

Tomsk · 23 Sep 2007 at 21:58

if cell A1=1400 & B1=1945 then your answer = (TIME(B1/100,MOD(B1,100),0)-TIME(A1/100,MOD(A1,100),0))*24. You may have to change the number format to number with 2 decimal places

Drukk · 23 Sep 2007 at 22:13

I have no idea how that works but it does! Awesome stuff, thanks a lot

Drukk · 30 Mar 2008 at 21:37

I have been using the above formula for awhile now and it works great but I was wondering if there was a way of modifying it so if the hours go over to the next day it would still work. Mainly due to having to work the odd nightshift.

example : start time 2030, end time 0300. Using the present formula this would give -17.5. I need it to give 6.5.

Any help would be great as Im not to good at excel stuff

Tomsk · 30 Mar 2008 at 22:06

= (TIME(B1/100,MOD(B1,100),0)-TIME(A1/100,MOD(A1,100),0)+(A1>B1))*24

Drukk · 31 Mar 2008 at 19:55

Really big thanks for that, it works a treat and is now incorporated into my excel yearly timesheet.