|Please see below for the original post...|
>> Original Post Starts <<
How can you convert timeclock time to get hours and minutes worked when you need the 10ths of an hour worked?
EXAMPLE: My paycheck says I worked 16:45 hours.
We clock in using a standard timeclock. I know there is a way to use excel to create a spreadsheet that will convert the time.
I'm sorry - it says I worked 16.45 hours, not 16:45 hours......
it's the .45 that gets me?
Is the ".45" 45 minutes, or .45 of an hour?
Assume the figure 16.45 is in cell A1, then
[Nb. the 'ROUND' function rounds 0.5 down, so I have added a small fraction into the equation]:
1) If its 45 minutes, then to get the 'tenths of an hour' out you could use the formula:
2) If its 16 + 0.45 hours, then to get the 'tenths of an hour' out, just seperate the integer part off, then round to 1dp:
Hope thats what you were after
It's .45 of an hour. So how exactly would I do that?
Thanks for your reply.
Well, you should use my suggestion number two to work out how many tenths of an hour are there, but is the answer to 16.45:
1) 5 tenths of an hour
2) 165 tenths of an hour?
My guess is that it is 45/100ths of an hour?
Does that answer your question back to me?
I tried both formulas that you gave me and neither one works? It gives me =Value?
Any other suggestions?
I got your formula to work - but that is not what I want.
If I clock in at 7:17AM and then clock out at 4:27PM - I want the excel spreadsheet to properly calculate the time worked.
Yes - I realised I had misunderstood your original post. Sorry about that.
The most helpful thing would be if you can give me EXACTLY what appears in a cell.
So, say you have 7:17AM and 4:27PM then you could use:
[Assuming that the first time is in cell A1, and the second in cell A2]
This will give you the time worked in hours. It assumes that you start before 10AM and finish after 12:59PM and before 10PM
To get this doing what you want, though, I would also need to know exactly what format the cells are, and whether these appear automatically, or are typed in manually.
The Cells are formated as Time and Type 1:30 PM
So Cell A1 (Punch In) would be 7:40
Cell A2 (Punch Out) would be 4:15
So if I punched in at 7:40AM and out at 4:15PM - I need to know how many hours I worked when they are using 100ths of an hour.
Thanks for all you help so far.
Ahhhhhhhhhhhh! You are using the Time format!
Then its intensely easy!
Got it! Thanks - but I had to change the cell numbers. But again many thanks for you help!
<< END of Original Post >>
Got a twist to this thing. My niece has to work 8 minutes of each 15 minute block in order to get paid for the whole 15 minutes.
So if she works 7am to 4:08 pm she would be paid until 4:15pm. If she worked until 4:07pm she would only be paid to 4:00pm. Is tehre a way to automatically round up and down based on this 8 minute rule?