Converting Time Clock Time

Sony
July 28, 2007 at 17:48:21
Specs: Windows Vista Ultimate, Intel 2 Ghz
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..


See More: Converting Time Clock Time

Report •


#1
July 30, 2007 at 11:59:53
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.


Report •

#2
July 30, 2007 at 18:54:56
Oh yeah...

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



Report •

#3
August 3, 2007 at 11:44:27
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?


Report •

Related Solutions

#4
August 3, 2007 at 15:24:05
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.25

I believe those are the results you were looking for.


Report •


Ask Question