Time Rounding VBA (Custom Function?)

[david000]

Tenth	Rounded	Clock	Tenth	Rounded	Clock
of Hr	Time	Range	of Hr	Time	Range
0	:00	:58-:03	0.5	:30	:28-:33
0.1	:06	:04-:09	0.6	:36	:34-:39
0.2	:12	:10-:15	0.7	:42	:40-:45
0.3	:18	:16-:21	0.8	:48	:46-:51
0.4	:24	:22-:27	0.9	:54	:52-:57

I want to round times like the above chart, but I'm having a really hard time with this. I need some incite into the workings of payroll clocks etc...

Public Function ROUNDTIME(MIN As Variant) As Variant
Application.Volatile
Select Case MIN
    Case 1 To 3:    ROUNDTIME = 0
    Case 4 To 9:    ROUNDTIME = 0.1
    Case 10 To 15:  ROUNDTIME = 0.2
    Case 16 To 21:  ROUNDTIME = 0.3
    Case 22 To 27:  ROUNDTIME = 0.4
    Case 28 To 33:  ROUNDTIME = 0.5
    Case 34 To 39:  ROUNDTIME = 0.6
    Case 40 To 45:  ROUNDTIME = 0.7
    Case 46 To 51:  ROUNDTIME = 0.8
    Case 52 To 57:  ROUNDTIME = 0.9
    Case Else:      ROUNDTIME = 0
End Select
End Function

I have a time system with a scheduler and I have a punch out time that indicates they worked over like, 2 pm to 10 pm = 8.0 , then if they work and extra 4 min I can use my formula to add the appropriate rounded minutes but it fails badly at the half hour.

Any help would be great!

Edit: The attachment is a bit confusing, what I want is the user to be able to ENTER the in/out times from the clock and my custom function or whatever rounds to the correct decimal adding the two together. Their scheduled hours and their actual hours worked.


For example the int portion of 2pm to 10 pm is 8 and the decimal portion could be 0.1 if they punched out between 4 minutes to 9 minutes after their scheduled time. Together they are 8.1 that's good, and I get that far, but times that are starting and ending at the half hour give me trouble, like this 130 pm to 930 pm is 8.0 if they punch out at 936 I want to add 6 minutes not 36 minute like this, 8.1 NOT 8.6.



Thanks.

[Kenneth Hobs]

I am not sure that I would want to be punished for working more than others. e.g. Row 2 is 9.1, not 8.1.

From row 2, copy down: =ROUND((E2-D2)*24,2)
Use General as the number format. This method does need a tweak if the times are on two different days.

Most would round hours worked to nearest 0.25 hours...

[david000]

I am not sure that I would want to be punished for working more that others. e.g. Row 2 is 9.1, not 8.1.

Ooops!

Thanks Ken.

what's is take to get a result like; 13:30 to 21:45 to look like 8.2? Is there a formatting trick? In other words I want to drop the 5 for the 8.25.

Don't laugh, I mean something like this, =LEFT(TEXT(ROUND((E2-D2)*24,2),"0.00"),3)

[Kenneth Hobs]

Change two decimal places to 1 and setting the number format to one place is better than a text format.
=ROUND((E2-D2)*24,1) = 9.3

9.3 is 9.25 rounded. If you want truncated then:
=FLOOR((E2-D2)*24,0.1) = 9.2

[david000]

That looks right thanks! It still bugs me that 9 minutes after the hour is supposed to be 0.1

[Aussiebear]

[QUOTE=david000;355155]That looks right thanks! It still bugs me that 9 minutes after the hour is supposed to be 0.1 [/QUOTE

Normally 9 minutes would be 0.15

[david000]

Normally 9 minutes would be 0.15

That's right Aussiebear, I was wondering how digital time-clocks for payroll make it "look" like 0.1 as opposed to 0.15 like the chart in my first question, I've never been able to find anywhere how to mimic that in Excel. I'm struggling with how to even ask the question properly.

[p45cal]

…and another:
=MROUND(E2-D2-1/86400,1/240)*24
format the cell as General (Excel always wants to give the cell a time format).
You might be happy enough with:
=MROUND(E2-D2,1/240)*24
the only difference being 1 second where:
.0 becomes .1 (00:03:00 v. 00:02:59)
.1 becomes .2 (00:09:00 v. 00:09:01)
etc.

[p45cal]

duplicate deleted.

[david000]

Thanks p45cal you're first formula is perfect and handles shift that span midnight with the usual tweak. I know there was and easy answer you guys a the best!

[david000]

Delete duplicate post


We need an app for this site.