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.

Any suggestions?

=======================

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:

=ROUND((A1+0.001-INT(A1))*10/6,1)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:

=ROUND(A1+0.001-INT(A1),1)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

or

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.

Any Suggestions?

===========================

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:

=((((VALUE(LEFT(A2,1))+12)*60)+VALUE(MID(A2,3,2)))-((VALUE(LEFT(A1,1))*60)+VALUE(MID(A1,3,2))))/60

[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!

=ROUND((A2-A1)*24,2)

================================Got it! Thanks - but I had to change the cell numbers. But again many thanks for you help!

=====================================

<< END of Original Post >>NEW QUESTION

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?

Thanks..

For your example, how about this... =ROUND((MROUND(A7,1/(24*4))-A6)*24,2)

Where A6 = Clock In, A7 = Clock Out

But what happens if the worker clocks in at 7:07 AM? Does (s)he also get paid for the full 15 minute block that started at 7:00AM? Sounds like a way to steal 13.8 minutes every day. Start at 7 minutes into a quarter hour and quit 8 minutes in.

Oh yeah... MROUND - If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

Doesn't work. It gives me FALSE. On my Spreadsheet, A2 is Clock-in and B2 is clock-out with the result in C2. Any suggestions?

FALSE? I can't think of a way that my formula, as written, would return a FALSE. There are no logical functions involved nor is it checking for an equality, such as =A2=B2. Are you sure you haven't altered my formula other than to change the cell references? If you can't duplicate the results shown below, e-mail me the spreadsheet and I'll take a look. I've PM'd an email address for you to use.

Here's what I just did:

A2 shows 7:00 AM, formatted as Time 1:30 PM

B2 shows 4:07 PM, formatted as Time 1:30 PM

C2 contains:

=ROUND((MROUND(B2,1/(24*4))-A2)*24,2) formatted as Number, 2 decimal places.With B2 reading 4:07 PM, C2 shows 9.00

With B2 reading 4:08 PM, C2 shows 9.25I believe those are the results you were looking for.

Ask Your Question

Weekly Poll

Do you think cellular roaming fees are unfair?

Discuss in The Lounge

Poll History