Time Calculation thought process

G

Gina Whipp

Hello All,

I am at an impass and sometimes just seeing other ideas helps me think it
out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am means
you lose 30 minutes). If you leave early you are docked in 15 minute
increments. There are no exceptions, you can't 'make-up' the time. (Glad I
don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) / 15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut)) /
15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
 
R

Robert Morley

You're so close...

15*Int([aTimeDecimal]/15)

....notice the change of where the closing bracket goes.



Rob
 
G

Gina Whipp

Robert,

That's not working either way...
8:01 AM - 5:00 PM comes out to 7.83 which I want to calculate to 7.75. The
calculation you corrected me out on shows 7 which is what it showed before.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
Robert Morley said:
You're so close...

15*Int([aTimeDecimal]/15)

...notice the change of where the closing bracket goes.



Rob

Gina Whipp said:
Hello All,

I am at an impass and sometimes just seeing other ideas helps me think it
out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) /
15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut)) /
15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
 
R

Robert Morley

Oh, you're in hours, with minutes as a fraction. Got it.

In that case, your calculation becomes:

Int([aTimeDecimal]*4)/4



Rob

Gina Whipp said:
Robert,

That's not working either way...
8:01 AM - 5:00 PM comes out to 7.83 which I want to calculate to 7.75.
The calculation you corrected me out on shows 7 which is what it showed
before.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
Robert Morley said:
You're so close...

15*Int([aTimeDecimal]/15)

...notice the change of where the closing bracket goes.



Rob

Gina Whipp said:
Hello All,

I am at an impass and sometimes just seeing other ideas helps me think
it out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) /
15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut))
/ 15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

Looks like that got it! I spent all day trying to use minutes against
fractions! (I told my doctor 2 cups of coffee a day wasn't enough!!!)

Big THANKS!
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
Robert Morley said:
Oh, you're in hours, with minutes as a fraction. Got it.

In that case, your calculation becomes:

Int([aTimeDecimal]*4)/4



Rob

Gina Whipp said:
Robert,

That's not working either way...
8:01 AM - 5:00 PM comes out to 7.83 which I want to calculate to 7.75.
The calculation you corrected me out on shows 7 which is what it showed
before.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Robert Morley said:
You're so close...

15*Int([aTimeDecimal]/15)

...notice the change of where the closing bracket goes.



Rob

Hello All,

I am at an impass and sometimes just seeing other ideas helps me think
it out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn))
/ 15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut))
/ 15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) /
60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
Click to expand...
 
G

George Nicholson

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the time.
(Glad I don't work there!)
Click to expand...

hmm, and I could have sworn that Walmart or somebody got slapped down hard
by the courts for doing some variation of this fairly recently.

Probably wishful thinking...



Gina Whipp said:
Hello All,

I am at an impass and sometimes just seeing other ideas helps me think it
out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the time.
(Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) /
15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut)) /
15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
Click to expand...
 
G

Gina Whipp

Hey, I only do what I'm contracted to do. Try as I might to point out that
this could cause problems I still must do as I am paid to do.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
George Nicholson said:
If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)
Click to expand...

hmm, and I could have sworn that Walmart or somebody got slapped down hard
by the courts for doing some variation of this fairly recently.

Probably wishful thinking...



Gina Whipp said:
Hello All,

I am at an impass and sometimes just seeing other ideas helps me think it
out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) /
15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut)) /
15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
 
G

George Nicholson

I still must do as I am paid to do.

Absolutely no argument on that score.

but, if you haven't already, you might consider handling the 15 minute
increment as a stored variable or constant, so that *if* they decide to
change it somewhere down the road, it wouldn't require rewriting tons of
queries/code.

.... but your invoice for rework services wouldn't need to reflect the time
saved from your foresight :)

HTH,

Gina Whipp said:
Hey, I only do what I'm contracted to do. Try as I might to point out
that this could cause problems I still must do as I am paid to do.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
George Nicholson said:
If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)
Click to expand...

hmm, and I could have sworn that Walmart or somebody got slapped down
hard by the courts for doing some variation of this fairly recently.

Probably wishful thinking...



Gina Whipp said:
Hello All,

I am at an impass and sometimes just seeing other ideas helps me think
it out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn)) /
15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut))
/ 15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) / 60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

Actually, George, that is exactly what I did. AND I told them I was doing
that, so it's in plain sight on my Invoice. Can't help it honest to the
core.
--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
George Nicholson said:
I still must do as I am paid to do.
Click to expand...

Absolutely no argument on that score.

but, if you haven't already, you might consider handling the 15 minute
increment as a stored variable or constant, so that *if* they decide to
change it somewhere down the road, it wouldn't require rewriting tons of
queries/code.

... but your invoice for rework services wouldn't need to reflect the time
saved from your foresight :)

HTH,

Gina Whipp said:
Hey, I only do what I'm contracted to do. Try as I might to point out
that this could cause problems I still must do as I am paid to do.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
George Nicholson said:
If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)

hmm, and I could have sworn that Walmart or somebody got slapped down
hard by the courts for doing some variation of this fairly recently.

Probably wishful thinking...



Hello All,

I am at an impass and sometimes just seeing other ideas helps me think
it out, so here it goes...

(TimeIn - TimeOut) - LunchBreak relatively easy calculation BUT

If you arrive late you are docked in 15 minute increments (so 7:16 am
means you lose 30 minutes). If you leave early you are docked in 15
minute increments. There are no exceptions, you can't 'make-up' the
time. (Glad I don't work there!)


What have I tried, that doesn't work:

15*Int(+[aTimeDecimal])/15


Don't know if this works because I can't get it to handle midnight...

Function HoursWorked(TimeIn As Variant, TimeOut As Variant) As Variant
'Thanks Allen Browne (found in the Newsgroup)
'Purpose: Return the difference in hours,
' rounding each argument to 15 minutes,
' and subtracting 30 minutes for lunch.
Dim dtTimeIn As Date 'TimeIn as a date, rounded to 15 min.
Dim dtTimeOut As Date 'TimeOut as a date, rounded to 15 min.
Dim lngMinutes As Long
Const lngcLunchBreak As Long = 30

'Initialize to Null
HoursWorked = Null

'Check the arguments aren't error/invalid.
If Not IsError(TimeIn) Or IsError(TimeOut) Then
If IsDate(TimeIn) And IsDate(TimeOut) Then
'Time since midnight, rounded to 15 minutes.
lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeIn))
/ 15)
dtTimeIn = DateAdd("n", lngMinutes, DateValue(TimeIn))

lngMinutes = 15 * CInt(DateDiff("n", #12:00:00 AM#, TimeValue(TimeOut))
/ 15)
dtTimeOut = DateAdd("n", lngMinutes, DateValue(TimeOut))

'subtract the lunch break, and convert to hours.
HoursWorked = (DateDiff("n", dtTimeIn, dtTimeOut) - lngcLunchBreak) /
60
End If
End If
End Function


Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

time formula 9
Unresolved Issue about totaling Downtime Hours 1
Function In SQL Problem 3
Workday calculations for weekends 9
IF calculation of Time 2
Work hours by Ken Sheridan. 16
Start and end times 4
call function module from query - having much trouble 8

Top