# 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 <>NEW QUESTIONGot 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

#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 OutBut 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 PMB2 shows 4:07 PM, formatted as Time 1:30 PMC2 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.00With B2 reading 4:08 PM, C2 shows 9.25I believe those are the results you were looking for.

Report •