Formula to work out hours worked

[peachybabe]

I'm trying to set up a simple Excel spreadsheet to log times at work. What I need to is a formula to work out the difference between a start time and a finish time, and to show that difference as a decimal. For example, if I start work at 09:00:00 and finish at 17:45:00 (written exactly as shown on the spreadsheet) I would have worked 8 hours 45 minutes. What formula can I enter to automatically calculate the time and show it as a decimal (as 7.75?)

[Hammond Egg]

Say you have the start time in A1 and the finish time in B1, with both cells formatted for time

Format cell C1 for number, with two decimal places

Put in C1 the formula:

=24*(b1-c1)

[Hammond Egg]

Say you have the start time in A1 and the finish time in B1, with both cells formatted for time

Format cell C1 for number, with two decimal places

Put in C1 the formula:

=24*(b1-a1)

[Hammond Egg]

Sorry - ignore the first answer, use the second!

[rojash]

Mind you, you'll have to do some weird math to get 8 hours 45 minutes to appear as 7.75 :-)

[peachybabe]

Yeh, I realised when it appeared on AB!
Just tried your formula and it sent the columns wild! Didn't work! I usually write formulae as =SUM(A1-B10) etc. What I'm having to do at the moment is to physically work out the exact times before I put hours worked in. My times are taken from a swipe in system. For example. I might swipe in at 08:51:00 and swipe out again at 16:32:00. So I sit with pen and paper to get to 7hrs 41mins. I then have to refer to a chart supplied by work to look up what 41 minutes are as a decimal, and then I can enter 7.? to get my times. Might be easier to give up work!

[ACtheTROLL]

good answers .... lol peachy

the simple answer is that excel knows!!
a1=08:15
b1=18:52
a1=b1-a1
answer 10:37

if you took 45 mins for lunch just add another column (c1) and the formula becomes b1-a1-c1
(9:52)

you can't go past midnight without a lot of faff

it does make sense.... but looks the wrong way round to me.

I still prefer peachybabe's suggestion.

[ACtheTROLL]

oops
line 6 should be c1=b1-a1

[peachybabe]

Okay everyone! Sussed it 100%!! Here goes.

Columns laid out in this order

a)Date b)Time In c)Time Out d)Hrs Worked

a) Usual date format
b) Time in written as 00:00:00
c) Time out written as 00:00:00
d) Format this column with =(C1-B1)*24

You can then add another column to subtract lunch so
e) Format as =D-0.5 (for half hr etc)

IT REALLY WORKS!!

Thanks to all for help and input. Guess I've not got any reason to give up work now!

[Ian b]

I was interested in this question as I have been keeping a log of hours worked for several years now, but have never managed to convert it to decimals automatically. So now I have the answer!!! If I can offer one small change that may make entering info a bit easier, format your 'time' cells thus-- Format / Cells / Custom / scroll down the 'type box', until you find "hh:mm", click OK. this will save you having to put the seconds in.