Round in Payroll situation

[sandrao]

How would you go about to round up the time to the nearest 15 minutes.

In this payroll program I have a StartTime and a EndTime. these run
off the comuter clock and record the exact time of start and end.

Start could be 8:05 and end could be 17:05. How can I have the time
rounded up to the nearest 15 minutes.

 

[aaron.kempf]

I'd just store the grain-- of the data-- in 15 minute chunks.

Instead of storign a date time field-- that says that they have worked
8 hours and 27 minutes.. and trying to change that--
record in the DB that they have recorded 34 slices of time.

That way- your numbers will always be accurate-- because they can't
store anything else

-Aaron

 

[James A. Fortune]

How would you go about to round up the time to the nearest 15 minutes.

In this payroll program I have a StartTime and a EndTime. these run
off the comuter clock and record the exact time of start and end.

Start could be 8:05 and end could be 17:05. How can I have the time
rounded up to the nearest 15 minutes.

Using my duration functions from the following thread:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/be7e101c5176ebd8

Note: If the person is going to punch in for a period that is longer
than 68 years another method should be used .

Available functions:

DDHHNNSS2Seconds(strTime As String)

Seconds2DDHHNNSS(lngTotalSeconds As Long)

AddDDHHNNSS(strTime1 As String, strTime2 As String)

SubtractDDHHNNSS2Seconds(strTime1 As String, strTime2 As String)

Note: The functions could also be changed to omit full days and use
floating point minutes instead to obviate the conversion to minutes

15 minute Ceiling: -Abs(-X * 15) / 15, where x is in minutes

Steps:

1. Get the start and stop times into the expected format

E.g.:

"00:" & Format("8:05", "hh\:nn\:ss") => 00:08:05:00
"00:" & Format("17:05", "hh\:nn\:ss") => 00:17:05:00

2. Convert to seconds, then minutes

E.g.:

DDHHNNSS2Seconds("00:" & Format("8:05", "hh\:nn\:ss")) => 29100 seconds
DDHHNNSS2Seconds("00:" & Format("17:05", "hh\:nn\:ss")) => 61500 seconds

SubtractDDHHNNSS2Seconds("00:" & Format("17:05", "hh\:nn\:ss"), "00:" &
Format("8:05", "hh\:nn\:ss")) / 60 => 540 minutes

3. Apply the Ceiling function

-Int(-SubtractDDHHNNSS2Seconds("00:" & Format("17:05", "hh\:nn\:ss"),
"00:" & Format("8:05", "hh\:nn\:ss")) / 4) / 15 => 540 minutes

4. Convert to seconds

-Int(-SubtractDDHHNNSS2Seconds("00:" & Format("17:05", "hh\:nn\:ss"),
"00:" & Format("8:05", "hh\:nn\:ss")) / 4) * 4 => 32400

5. Convert back to a time string that indicates the duration

Seconds2DDHHNNSS(-Int(-SubtractDDHHNNSS2Seconds("00:" & Format("17:05",
"hh\:nn\:ss"), "00:" & Format("8:05", "hh\:nn\:ss")) / 4) * 4) =>
00:09:00:00

I.e., 0 Days, 9 Hours, 0 Minutes, 0 Seconds

In using that expression in a query, "8:05" and "17:05" would be
replaced by the table field names. AddDDHHNNSS() could be used
repeatedly to add together a set of durations, or it could be changed to
accept an array of durations. Taking account of days is a relatively
clean way to account for both durations crossing midnight and adding
durations whose total is greater than or equal to 24 hours. Hint: You
can use the DateDiff() function to get the number of minutes in a
duration based on start and stop Date/Time fields using the complete
Date/Time field rather than using just the hours and minutes from a time
ticket.

Other posters here, such as Douglas Steele, have posted somewhat similar
solutions.

Post back if you need more help.

James A. Fortune
(e-mail address removed)

 

[aaron.kempf]

hey James-- I just wanted to chirp in

-Abs(-X * 15) /

this / means 'integer division' right?

That is how you round? Break it into an integer and then multiply it
back times 15.

It just seems to me like this is going to consistently short-change
people..
It seems to me like you should add 7 minutes and 29 seconds before
doing that (implicitly).

I think that is what would be required to 'round up'

Thanks & HTH

-Aaron

 

[James A. Fortune]

hey James-- I just wanted to chirp in

-Abs(-X * 15) /

this / means 'integer division' right?

Thanks for catching that. I should have shown -Int(-X * 15) / 15 (the
sample calculation uses Int() properly). It is a combination of the
Ceiling function -Int(-X), which can be verified by graphing and
comparing to the Int() function to be a valid Ceiling function for all
X, and a simple change of variable in the X and Y directions to scale
the graph to round up to multiples instead of to the next higher
integer. The scaling can also go the other way:

-Int(-X / 10) * 10

to round up to the nearest 0.1.

Integer division, per se, is often done with the \ operator. I.e., X \
10 rather than Int(X/10) and can be used to simplify the expression when
rounding up to, say, the nearest 0.1.

James A. Fortune
(e-mail address removed)