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

How concerned are you about the OpenSSL "Heartbleed" bug?

Discuss in The Lounge

Poll History