473,223 Members | 1,772 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,223 software developers and data experts.

date/time problem

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
6 6372
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jay | last post by:
I previously posted this question under Visual Basic newsgroup, but was advised to re-post here. I'm hoping someone can help me solve an issue I'm having with VB.Net and Access 2000. Here's...
7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
1
by: Raghu | last post by:
Hello... I am running into a problem while running a query..can some1 help.. this is the query : ************** SELECT * from Table S where S.dtDate1 BETWEEN...
18
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the...
2
by: Riegn Man | last post by:
I have a problem with access and our time clocks. We have time clocks that put out a .log file with the badge swipes for everybody. There is one .log file for each day. I am pulling that data...
12
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as...
44
by: user | last post by:
Hi, Let's say I have 2 dates in the b/m format: Date 1 and date 2 How do I check whether Date2 is later than Date 1? Date1. 21-Nov-2006 09:00:00 PM
6
by: Geoff Cox | last post by:
Hello, at the moment I can add the combined date and time into MySQL using php $dt1 = date("Y-m-d H:i:s"); is it possible to add the date and time separately? I thought it might be
10
by: WebCM | last post by:
There is a function: http://paste.ubuntu.com/21865 It needs GMT date in YYYY-MM-DD HH:MM:SS format - in SQL: datetime. If date is the same as today, the function returns "Today". There is one...
16
by: W. eWatson | last post by:
Are there some date and time comparison functions that would compare, say, Is 10/05/05 later than 09/22/02? (or 02/09/22 format, yy/mm/dd) Is 02/11/07 the same as 02/11/07? Is 14:05:18 after...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.