By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,393 Members | 1,586 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,393 IT Pros & Developers. It's quick & easy.

date/time problem

P: n/a
Hi Guru's,
I have created a database to monitor hours I have worked as our payroll
department are so crap. I work nights most of the time but occasionally I
have to work on days. Between the hours of 18:00 and 06:00 colleagues get an
unsocial shift allowance. I have managed to get it to work out my gross
income for a 4 week period accounting for overtime ect but I am struggling
to get the unsocial shift allowance included.

All is well if I do a 22:00 - 06:00 shift but if I do a 14:00 - 22:00 shift,
4 hrs will be paid normal whilst the other 4 will include the unsocial. How
can I get it database to calculate 4 hrs normal and 4 hrs with the unsocial.

I am using the long date format for my start and finish times and am using
Access XP.

Thanks in advance,

Mark
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Mark Reed wrote:
Hi Guru's,
I have created a database to monitor hours I have worked as our payroll
department are so crap. I work nights most of the time but occasionally I
have to work on days. Between the hours of 18:00 and 06:00 colleagues get an
unsocial shift allowance. I have managed to get it to work out my gross
income for a 4 week period accounting for overtime ect but I am struggling
to get the unsocial shift allowance included.

All is well if I do a 22:00 - 06:00 shift but if I do a 14:00 - 22:00 shift,
4 hrs will be paid normal whilst the other 4 will include the unsocial. How
can I get it database to calculate 4 hrs normal and 4 hrs with the unsocial.

I am using the long date format for my start and finish times and am using
Access XP.

Thanks in advance,

Mark


You have to know something about dates. Date() is based on the day of
the date at 00:00. Now() is based on the day of the date and the time
from 00:00:00 to 23:59:59.

If you have a value of 6/1/2004 at 11am and you ask for all records = to
6/1/2004 or between 6/1/2004 and 6/1/2004.

I suppose you can create a query that uses DateAdd() or DateDiff() to
create some column values. In fact, you may want to create a query that
does that and then create another query using the first query to come up
with the final results.

Personally, I would write a function. Pass the start time and the end
time. Ex:
TotHours : CalcHours([StartDateTime],[EndDateTime])

and CalcHours would look something like
Fuctnion CalcHours(datStart As Date, datEnd as Date)

Now using a bunch of Ifs and Elses you can cacl it out and return the
values.

Nov 13 '05 #2

P: n/a
I may not have been clear. I have already figured out how to work out how
many hours I have worked by using

=IIf([Type]<>"Overtime",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((D
ateDiff("n",[start_time],[end_time])/60)-((DateDiff("n",[start_time],[end_ti
me])/60)-8)),(DateDiff("n",[start_time],[end_time])/60)),0)

and I have figured out how to work out if I have work over or under my
rostered hours by using
=IIf([Type]="In",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((DateDiff
("n",[start_time],[end_time])/60)-8),((DateDiff("n",[start_time],[end_time])
/60)-8)),IIf([Type]="Overtime",(DateDiff("n",[start_time],[end_time])/60),0)
)

What I need to do is get it to work out how many of the hours worked were
between 18:00 and 06:00 and how many were not. I cannot see how you would do
that from what you have given me so far!!!

Thanks for your help,

Mark

"Salad" <oi*@vinegar.com> wrote in message
news:p5******************@newsread1.news.pas.earth link.net...
Mark Reed wrote:
Hi Guru's,
I have created a database to monitor hours I have worked as our payroll department are so crap. I work nights most of the time but occasionally I have to work on days. Between the hours of 18:00 and 06:00 colleagues get an unsocial shift allowance. I have managed to get it to work out my gross
income for a 4 week period accounting for overtime ect but I am struggling to get the unsocial shift allowance included.

All is well if I do a 22:00 - 06:00 shift but if I do a 14:00 - 22:00 shift, 4 hrs will be paid normal whilst the other 4 will include the unsocial. How can I get it database to calculate 4 hrs normal and 4 hrs with the unsocial.
I am using the long date format for my start and finish times and am using Access XP.

Thanks in advance,

Mark


You have to know something about dates. Date() is based on the day of
the date at 00:00. Now() is based on the day of the date and the time
from 00:00:00 to 23:59:59.

If you have a value of 6/1/2004 at 11am and you ask for all records = to
6/1/2004 or between 6/1/2004 and 6/1/2004.

I suppose you can create a query that uses DateAdd() or DateDiff() to
create some column values. In fact, you may want to create a query that
does that and then create another query using the first query to come up
with the final results.

Personally, I would write a function. Pass the start time and the end
time. Ex:
TotHours : CalcHours([StartDateTime],[EndDateTime])

and CalcHours would look something like
Fuctnion CalcHours(datStart As Date, datEnd as Date)

Now using a bunch of Ifs and Elses you can cacl it out and return the
values.

Nov 13 '05 #3

P: n/a
Mark Reed wrote:
I may not have been clear. I have already figured out how to work out how
many hours I have worked by using

=IIf([Type]<>"Overtime",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((D
ateDiff("n",[start_time],[end_time])/60)-((DateDiff("n",[start_time],[end_ti
me])/60)-8)),(DateDiff("n",[start_time],[end_time])/60)),0)

and I have figured out how to work out if I have work over or under my
rostered hours by using
=IIf([Type]="In",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((DateDiff
("n",[start_time],[end_time])/60)-8),((DateDiff("n",[start_time],[end_time])
/60)-8)),IIf([Type]="Overtime",(DateDiff("n",[start_time],[end_time])/60),0)
)

What I need to do is get it to work out how many of the hours worked were
between 18:00 and 06:00 and how many were not. I cannot see how you would do
that from what you have given me so far!!!
IOW, you can't program. You should have said that you aren't a
programmer. Besides, there was so much info lacking I couldn't help
further.

What is start time and what is end time? Are they Time fields (14:00)
or DateTime fields (6/1/2004 14:00)?

What happens if you do some shifts from 6/1 to 6/4 and the pay period
break in on 6/2?

If you provide the info, I might be able to write a quick routine for you.

Thanks for your help,

Mark

"Salad" <oi*@vinegar.com> wrote in message
news:p5******************@newsread1.news.pas.earth link.net...


Nov 13 '05 #4

P: n/a
Hi Salad,
Sorry about the lack of info. You are right, I have no idea about
programming at all.
Start end and fields are in date & time format (dd/mm/yy hh:nn).
The pay period is based on a 4 weekly period.
The cut off time is 06:00 of a particular day but if I was to start a shift
at 05:00 on the cuff off day, the entire duration of the shift I work would
be included in the total hours I have worked prior to the cut off.

Hope this helps,

Mark

"Salad" <oi*@vinegar.com> wrote in message
news:BM******************@newsread2.news.pas.earth link.net...
Mark Reed wrote:
I may not have been clear. I have already figured out how to work out how many hours I have worked by using

=IIf([Type]<>"Overtime",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((D ateDiff("n",[start_time],[end_time])/60)-((DateDiff("n",[start_time],[end_ti me])/60)-8)),(DateDiff("n",[start_time],[end_time])/60)),0)

and I have figured out how to work out if I have work over or under my
rostered hours by using
=IIf([Type]="In",IIf((DateDiff("n",[start_time],[end_time])/60)>8,((DateDiff ("n",[start_time],[end_time])/60)-8),((DateDiff("n",[start_time],[end_time]) /60)-8)),IIf([Type]="Overtime",(DateDiff("n",[start_time],[end_time])/60),0) )

What I need to do is get it to work out how many of the hours worked were between 18:00 and 06:00 and how many were not. I cannot see how you would do that from what you have given me so far!!!


IOW, you can't program. You should have said that you aren't a
programmer. Besides, there was so much info lacking I couldn't help
further.

What is start time and what is end time? Are they Time fields (14:00)
or DateTime fields (6/1/2004 14:00)?

What happens if you do some shifts from 6/1 to 6/4 and the pay period
break in on 6/2?

If you provide the info, I might be able to write a quick routine for you.

Thanks for your help,

Mark

"Salad" <oi*@vinegar.com> wrote in message
news:p5******************@newsread1.news.pas.earth link.net...

Nov 13 '05 #5

P: n/a
Mark Reed wrote:
Hi Salad,
Sorry about the lack of info. You are right, I have no idea about
programming at all.
Start end and fields are in date & time format (dd/mm/yy hh:nn).
The pay period is based on a 4 weekly period.
The cut off time is 06:00 of a particular day but if I was to start a shift
at 05:00 on the cuff off day, the entire duration of the shift I work would
be included in the total hours I have worked prior to the cut off.
What a Pita.
Hope this helps,


I wrote a function for you. The problem was a bit more complicated that
I initially thought. I did not have enough info to make it foolproof
but it's close.

You pass the shift (R or O), start/end date/tims, and the begin/ending
pay period.

The value it returns back is in minutes.

One thing you should note. Let's say you punched in at 5:45 and punched
out at 2:15 and your shift is between 6 and 2. This will calc out at 8
1/2 hours. If you punched in at 7:01 and out at 8:00, it would calc to
59 minutes.

In your query builder you could create an expression. Let's say the
payperiod is 6/1/2004 to 6/30/2004. You could enter
RegMinutes : CalcHrs("R",[Start_Time],[End_Time], _
#6/1/2004#,#6/30/2004#)

OTMinutes : CalcHrs("R",[Start_Time],[End_Time], _
#6/1/2004#,#6/30/2004#)

Public Function CalcHrs(strShift As String, _
datStart As Date, _
datEnd As Date, _
datPStart As Date, _
datPEnd As Date) As Long

'strShift R = Regular hours, O = OT
'assumes start/end times are date fields with times
'datStartPay, datEndPay are date fields without times

Dim intReg As Integer
Dim intOT As Integer
Dim datCheckStart As Date
Dim datCheckEnd As Date
Dim intMinutes As Integer
Dim datPPStart As Date
Dim datPPEnd As Date

'create a date field w/o the time of the start date
datPPStart = DateSerial(Year(datStart), Month(datStart), Day(datStart))

'if the person starts or after 6am, don't calc. 0 hours
If datPPStart = datPStart And Hour(datStart) >= 6 Or _
datPPStart = datPEnd And Hour(datStart) >= 6 Then

Exit Function
End If

'remove seconds from start/end time
datCheckStart = DateAdd("s", Second(datStart) * -1, datStart)
datCheckEnd = DateAdd("s", Second(datEnd) * -1, datEnd)

'get number of minutes from start time to next hour
'Ex: start at 5:30, 30 minutes till 6:00
intMinutes = 60 - Minute(datCheckStart)

If intMinutes <> 0 Then
If Hour(datCheckStart) >= 6 And Hour(datCheckStart) < 18 Then
intReg = intMinutes
Else
intOT = intMinutes
End If

'add those minutes to get the next next hour
'ex: start at 5:30, add 30 so starttime now is 6:00
datCheckStart = DateAdd("n", intMinutes, datCheckStart)
End If

'now get the ending minutes
intMinutes = Minute(datCheckEnd)
If intMinutes <> 0 Then
If Hour(datCheckEnd) >= 6 And Hour(datCheckEnd) < 18 Then
intReg = intMinutes
Else
intOT = intMinutes
End If

'bring the ending hour to 0 minutes, 0 seconds.
'ex: punch out at 7:30, send to 7:00
datCheckEnd = DateAdd("n", intMinutes * -1, datCheckEnd)
End If

'loop through the hours of in/out
Do While Format(datCheckStart, "mm/dd/yyyy hh:nn") <> _
Format(datCheckEnd, "mm/dd/yyyy hh:nn")

If Hour(datCheckStart) >= 6 And Hour(datCheckStart) < 18 Then
intReg = intReg + 60
Else
intOT = intOT + 60
End If
datCheckStart = DateAdd("h", 1, datCheckStart)

Loop

CalcHrs = IIf(strShift = "R", intReg, intOT)

End Function
Nov 13 '05 #6

P: n/a
Thanks for all your help on this. I have not tried it yet but will be taking
it to work with me to give it a go with a little assistance from a colleague
with more experience than me. I will keep you posted.

Thanks again,

Mark
"Salad" <oi*@vinegar.com> wrote in message
news:oS******************@newsread1.news.pas.earth link.net...
Mark Reed wrote:
Hi Salad,
Sorry about the lack of info. You are right, I have no idea about
programming at all.
Start end and fields are in date & time format (dd/mm/yy hh:nn).
The pay period is based on a 4 weekly period.
The cut off time is 06:00 of a particular day but if I was to start a shift at 05:00 on the cuff off day, the entire duration of the shift I work would be included in the total hours I have worked prior to the cut off.
What a Pita.

Hope this helps,


I wrote a function for you. The problem was a bit more complicated that
I initially thought. I did not have enough info to make it foolproof
but it's close.

You pass the shift (R or O), start/end date/tims, and the begin/ending
pay period.

The value it returns back is in minutes.

One thing you should note. Let's say you punched in at 5:45 and punched
out at 2:15 and your shift is between 6 and 2. This will calc out at 8
1/2 hours. If you punched in at 7:01 and out at 8:00, it would calc to
59 minutes.

In your query builder you could create an expression. Let's say the
payperiod is 6/1/2004 to 6/30/2004. You could enter
RegMinutes : CalcHrs("R",[Start_Time],[End_Time], _
#6/1/2004#,#6/30/2004#)

OTMinutes : CalcHrs("R",[Start_Time],[End_Time], _
#6/1/2004#,#6/30/2004#)

Public Function CalcHrs(strShift As String, _
datStart As Date, _
datEnd As Date, _
datPStart As Date, _
datPEnd As Date) As Long

'strShift R = Regular hours, O = OT
'assumes start/end times are date fields with times
'datStartPay, datEndPay are date fields without times

Dim intReg As Integer
Dim intOT As Integer
Dim datCheckStart As Date
Dim datCheckEnd As Date
Dim intMinutes As Integer
Dim datPPStart As Date
Dim datPPEnd As Date

'create a date field w/o the time of the start date
datPPStart = DateSerial(Year(datStart), Month(datStart),

Day(datStart))
'if the person starts or after 6am, don't calc. 0 hours
If datPPStart = datPStart And Hour(datStart) >= 6 Or _
datPPStart = datPEnd And Hour(datStart) >= 6 Then

Exit Function
End If

'remove seconds from start/end time
datCheckStart = DateAdd("s", Second(datStart) * -1, datStart)
datCheckEnd = DateAdd("s", Second(datEnd) * -1, datEnd)

'get number of minutes from start time to next hour
'Ex: start at 5:30, 30 minutes till 6:00
intMinutes = 60 - Minute(datCheckStart)

If intMinutes <> 0 Then
If Hour(datCheckStart) >= 6 And Hour(datCheckStart) < 18 Then
intReg = intMinutes
Else
intOT = intMinutes
End If

'add those minutes to get the next next hour
'ex: start at 5:30, add 30 so starttime now is 6:00
datCheckStart = DateAdd("n", intMinutes, datCheckStart)
End If

'now get the ending minutes
intMinutes = Minute(datCheckEnd)
If intMinutes <> 0 Then
If Hour(datCheckEnd) >= 6 And Hour(datCheckEnd) < 18 Then
intReg = intMinutes
Else
intOT = intMinutes
End If

'bring the ending hour to 0 minutes, 0 seconds.
'ex: punch out at 7:30, send to 7:00
datCheckEnd = DateAdd("n", intMinutes * -1, datCheckEnd)
End If

'loop through the hours of in/out
Do While Format(datCheckStart, "mm/dd/yyyy hh:nn") <> _
Format(datCheckEnd, "mm/dd/yyyy hh:nn")

If Hour(datCheckStart) >= 6 And Hour(datCheckStart) < 18 Then
intReg = intReg + 60
Else
intOT = intOT + 60
End If
datCheckStart = DateAdd("h", 1, datCheckStart)

Loop

CalcHrs = IIf(strShift = "R", intReg, intOT)

End Function

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.