formula for time worked

Now then guys - seeing as there have been a few off topic questions lately and I know there are some great mathematicians visit this site, I need a formula for working out actual time worked in hours & minutes. (This is for a spread-sheet not plc.)

I have a chap on loan to a company and he has been issued with a swipe card to clock in and out. They send me a weekly printout of his ‘in and out’ times.

Something like MONDAY IN 07:59 - OUT 17:16 etc, he does not work set hours.

I set up a spreadsheet to work it out but try as I might, I cannot get it right. I end up working the hours out on my fingers.

The above example should be 9 Hours, 17 minutes (not counting lunch break) but if I deduct ‘in’ time from ‘out’ time I get 9.57. I am sure there is a formulae for it - does anyone know it?

Use =TIMEVALUE("7:59") format to put in the times. It converts time to a number. Format the all cells as time and it will display

I  7:59  I  17:16  I  9:17  I

Goody,

Format two columns for time. I selected the first choice. (I think it is short time. 13:30) Then do a subtraction on the cells.

Bob O.

That's right, if everything your doing is in time, then you don't need to use TIMEVALUE.

Thanks Kevin and Bob, that is bang on the nail.
I never knew there was a timevalue command. (i do now lol)

In my job I am the technician, the secratary, the accountant, the cleaner and the IT man

Thnx its much appreciated.

I use this sheet. It subtracts for a half hour lunch. It gives a decimal hour result. Good for multiplying by a rate.

Thanks Rick - thats what I was trying to set up the spread-sheet to do.