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

calculate works sundays if date/time include both date and time

P: 59
In my table date/time field (starttime,endtime) include both date and time. How can I calculate worktime in query for each employee who works sundays?
for an example:
sometimes starttime and endtime is sunday and sometimes only endtime is sunday

for an examlpe:
eployee starttime endtime timeout
patric Jo.. 12/03/2006 21:00:00 13/03/2006 02:00:00 01:00:00
patric jo.. 25/03/2006 10:00:00 25/03/200614:00:00 02:00:00
Nov 28 '06 #1
Share this Question
Share on Google+
32 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
In my table date/time field (starttime,endtime) include both date and time. How can I calculate worktime in query for each employee who works sundays?
for an example:
sometimes starttime and endtime is sunday and sometimes only endtime is sunday

for an examlpe:
eployee starttime endtime timeout
patric Jo.. 12/03/2006 21:00:00 13/03/2006 02:00:00 01:00:00
patric jo.. 25/03/2006 10:00:00 25/03/200614:00:00 02:00:00
For example:

Expand|Select|Wrap|Line Numbers
  1.  
  2. IIf(Weekday([endtime],1),[endtime] + 1, [endtime])
  3.  
This function will return [endtime]+1(Monday) if [endtime] is a Sunday, else it will return [endtime].
Nov 28 '06 #2

P: 59
For example:

Expand|Select|Wrap|Line Numbers
  1.  
  2. IIf(Weekday([endtime],1),[endtime] + 1, [endtime])
  3.  
This function will return [endtime]+1(Monday) if [endtime] is a Sunday, else it will return [endtime].
I have three cases for calculation sunday in my table
1 if date beginning on saturday and ending on sunday
2 if date beginning on sunday and ending on sunday
3 if date beginning on sunday and ending on monday
for an example:
peter jo.. 12/03/2006 07:00:00 12/03/2006 15:00:00
peter jo.. 11/03/2006 21:00.00 12/03/2006 03:00:00
peter jo.. 12/03/2006 22:00:00 13/03/2006 01:00:00
if 12/03/2006 is sunday how can I calculate all this cases in one function?
Nov 29 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
I have three cases for calculation sunday in my table
1 if date beginning on saturday and ending on sunday
2 if date beginning on sunday and ending on sunday
3 if date beginning on sunday and ending on monday
for an example:
peter jo.. 12/03/2006 07:00:00 12/03/2006 15:00:00
peter jo.. 11/03/2006 21:00.00 12/03/2006 03:00:00
peter jo.. 12/03/2006 22:00:00 13/03/2006 01:00:00
if 12/03/2006 is sunday how can I calculate all this cases in one function?
I need more information.

When and where do you want this calculation to take place. In a query, control on a form, etc?

How is time out included in the calculation?

What are you trying to calculate? No of days, hours, or what?
Nov 30 '06 #4

P: 59
I need more information.

When and where do you want this calculation to take place. In a query, control on a form, etc?

How is time out included in the calculation?

What are you trying to calculate? No of days, hours, or what?
I want to see this calculation first in query then in form.
Time out isn't necessary be in calculation.
I try to make query(for all employees) in which can be seen total work time on sundays as hours and minutes, for one month or year.
Nov 30 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
I want to see this calculation first in query then in form.
Time out isn't necessary be in calculation.
I try to make query(for all employees) in which can be seen total work time on sundays as hours and minutes, for one month or year.
It's complicated. Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1,
  3. Hours([endtime]) - Hours([starttime]), 24 - Hours([starttime])),
  4. IIf(Weekday([endtime],1)=1,Hours([endtime]),0))) As SundayHours
  5. FROM YourTableNameHere
  6. GROUP BY eployee;
  7.  
Nov 30 '06 #6

NeoPa
Expert Mod 15k+
P: 31,615
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee, StartTime, EndTime, EndTime-StartTime
  2. FROM YourTable
  3. WHERE (1 In(Weekday(StartTime),Weekday(EndTime)))
This can be GROUPed or whatever, but should select the data correctly for you.
Dec 1 '06 #7

P: 59
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee, StartTime, EndTime, EndTime-StartTime
  2. FROM YourTable
  3. WHERE (1 In(Weekday(StartTime),Weekday(EndTime)))
This can be GROUPed or whatever, but should select the data correctly for you.
you code is good if employee start and finish work on sunday, but
I have case where employee start on saturday and finish on sunday.
His worktime on sunday is time after midnight.
Dec 3 '06 #8

P: 59
It's complicated. Try this ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1,
  3. Hours([endtime]) - Hours([starttime]), 24 - Hours([starttime])),
  4. IIf(Weekday([endtime],1)=1,Hours([endtime]),0))) As SundayHours
  5. FROM YourTableNameHere
  6. GROUP BY eployee;
  7.  
this code return me message that the hours is not defined
Dec 3 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
this code return me message that the hours is not defined
My apologies, I did this in a hurry. Hours should be Hour.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1,
  3. Hour([endtime]) - Hour([starttime]), 24 - Hour([starttime])),
  4. IIf(Weekday([endtime],1)=1,Hour([endtime]),0))) As SundayHours
  5. FROM YourTableNameHere
  6. GROUP BY eployee;
Dec 3 '06 #10

NeoPa
Expert Mod 15k+
P: 31,615
Expand|Select|Wrap|Line Numbers
  1. SELECT Employee, StartTime, EndTime, EndTime-StartTime
  2. FROM YourTable
  3. WHERE (1 In(Weekday(StartTime),Weekday(EndTime)))
you code is good if employee start and finish work on sunday, but
I have case where employee start on saturday and finish on sunday.
His worktime on sunday is time after midnight.
Barkarlo,

You should try it out.
It does work for any situation where either the start date OR the end date is a Sunday.
It's a little unusual I know but that is sometimes necessary in code (for brevity).
You can always redo it the long way with an OR if you prefer though.
Dec 3 '06 #11

P: 59
My apologies, I did this in a hurry. Hours should be Hour.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT eployee, Sum(IIf(Weekday([starttime],1)=1,IIf(Weekday([endtime],1)=1,
  3. Hour([endtime]) - Hour([starttime]), 24 - Hour([starttime])),
  4. IIf(Weekday([endtime],1)=1,Hour([endtime]),0))) As SundayHours
  5. FROM YourTableNameHere
  6. GROUP BY eployee;
yes that's it, thanks
Dec 4 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
yes that's it, thanks
You're Welcome.
Dec 5 '06 #13

P: 59
You're Welcome.
how can I see, in query, the workhours of an employee on holiday?
Dec 5 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
how can I see, in query, the workhours of an employee on holiday?
I don't understand the question. Assuming employees on holiday don't have workhours. Holidays are dynamic and hard to query against.

Mary
Dec 8 '06 #15

P: 59
I don't understand the question. Assuming employees on holiday don't have workhours. Holidays are dynamic and hard to query against.

Mary
I want to see workhours for state holiday not regular holiday
Dec 8 '06 #16

NeoPa
Expert Mod 15k+
P: 31,615
I want to see workhours for state holiday not regular holiday
Barkarlo,
While Mary's temporarily away can I step in here and ask for further clarification on her behalf. I can't see how your statement would clarify the situation. Maybe Mary will (She's always been better at understanding what people mean than I am) but just in case, it may help to explain a little more clearly.
I can say that no Office system has intrinsic values for any holidays (state or otherwise) so it may be that what you're after is a step too far anyway. I expect that Mary will post in here when she returns anyway.
Dec 8 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks Ade.

Barkolo,

The only way you can do this is to create a table (lookup list) of all the state holidays concerned. Include it in the query in something like the following.

Expand|Select|Wrap|Line Numbers
  1. IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=" & [StartTime] & " OR [HolidayDate]=" & [EndTime]),0)=0, <not a state holdiay>, )
  2.  
Mary
Dec 9 '06 #18

NeoPa
Expert Mod 15k+
P: 31,615
Mary,

I have one thing to say - 'Dates'.
Dec 9 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary,

I have one thing to say - 'Dates'.
Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this.

Should be ...

Expand|Select|Wrap|Line Numbers
  1. IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, <not a state holdiay>, )
  2.  
Mary
Dec 9 '06 #20

P: 59
Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this.

Should be ...

Expand|Select|Wrap|Line Numbers
  1. IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, <not a state holdiay>, )
  2.  
Mary
I don't understand the end of your code <not a state holiday>.Is
that a part of a table holiday or something else?
Dec 9 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't understand the end of your code <not a state holiday>.Is
that a part of a table holiday or something else?
This is not code it's an indication to put whatever code you need to return the value if it's not a state holiday. Similarly, <state holiday> is where you put the code to return the value if it is a state holiday.

Surrounding anything with <> means put in your own value.

Mary
Dec 9 '06 #22

P: 59
This is not code it's an indication to put whatever code you need to return the value if it's not a state holiday. Similarly, <state holiday> is where you put the code to return the value if it is a state holiday.

Surrounding anything with <> means put in your own value.

Mary
I wrote your code but return me following message " you may have entered an operand without an operator"
Dec 10 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post exactly what you've entered in full.
Dec 10 '06 #24

NeoPa
Expert Mod 15k+
P: 31,615
I wrote your code but return me following message " you may have entered an operand without an operator"
No-one can tell what's wrong with your code unless they get to see it Barkarlo.
Please indicate which line was yellow in the VBA window when you got the error message.
Dec 10 '06 #25

P: 59
Sorry Ade, as you said in a previous post. I was still tired and hungover when I did this.

Should be ...

Expand|Select|Wrap|Line Numbers
  1. IIf(nz(DLookup("[HolidayDate]", "tblHolidays", "[HolidayDate]=#" & [StartTime] & "# OR [HolidayDate]=#" & [EndTime] & "#"),0)=0, <not a state holdiay>, )
  2.  
Mary
I have "tblholiday" with the following fields:
No. holidaydate holidayname
1
and table "tblexploatation"
employee starttime endtime
Dec 11 '06 #26

P: 59
Can you post exactly what you've entered in full.
I have the table "tblholiday" with following fields:
no. holiday date holiday name
and "tblexploatation"
employee starttime endtime
date/time fields include both date and time
I want to see in a query how much time does the employee work on holidays.
Dec 11 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
Barkolo

If you don't post the query as it currently is we can't help you any further.

Mary
Dec 11 '06 #28

P: 59
Barkolo

If you don't post the query as it currently is we can't help you any further.

Mary
query is.
Expand|Select|Wrap|Line Numbers
  1.   IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,<not a state holiday>,)
when I click on datasheet view return me already written message. After click OK show wrong in state holiday.
Dec 11 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
query is.
IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,<not a state holiday>,)
when I click on datasheet view return me already written message. After click OK show wrong in state holiday.
I've told you before, '<not a state holiday>' is not code.


Expand|Select|Wrap|Line Numbers
  1. IIf(nz(Dlookup("[holidaydate]","tblholiday", "[holidaydate]=#"
  2. & [starttime] & "# OR [holidaydate]=#" & [endtime] &
  3. "#"),0)=0,IIf(Weekday([endtime],1),[endtime] + 1, [endtime]), <I can't
  4. put in the code for what to do if it is a state holiday because you
  5. haven't told me what that is>)
  6.  
also this is not actually a query it's just an if statement. Where do you want the resulting value?

Mary
Dec 12 '06 #30

NeoPa
Expert Mod 15k+
P: 31,615
query is.
Expand|Select|Wrap|Line Numbers
  1.   IIF(nz(dlookup("[holidaydate]","tblholiday", "[holidaydate]=#" &[starttime] & "# OR [holidaydate]=#" &[endtime] & "#"),0)=0,<not a state holiday>,)
when I click on datasheet view return me already written message. After click OK show wrong in state holiday.
That won't work.
Mary has already explained that to you in post #22.

Let me explain how this works Barkarlo.
You ask questions about how to do something; we try to give help; you take that help and apply it (intelligently) to your code.
Taking the explanation or example code and just dropping it in your database won't work.
I see this as a problem already perfectly well answered. You need to read what has been posted for you and act on it.
If, after that, you find you still have issues then come back for some more help.
If it's not clear you need to replace <not a state holiday> with the value you need if it's not a state holiday.
After the comma also needs the reverse value.
This code returns a function - It also needs to be used or it's a waste of time completely.
Dec 12 '06 #31

P: 59
I've told you before, '<not a state holiday>' is not code.


Expand|Select|Wrap|Line Numbers
  1. IIf(nz(Dlookup("[holidaydate]","tblholiday", "[holidaydate]=#"
  2. & [starttime] & "# OR [holidaydate]=#" & [endtime] &
  3. "#"),0)=0,IIf(Weekday([endtime],1),[endtime] + 1, [endtime]), <I can't
  4. put in the code for what to do if it is a state holiday because you
  5. haven't told me what that is>)
  6.  
also this is not actually a query it's just an if statement. Where do you want the resulting value?

Mary
I want to see,in query, only hours for an a employee who works on eastern holidays.
sorry, but with your code can't get result.
maybe I must more learn from book
thanks for trying help me.
Dec 12 '06 #32

NeoPa
Expert Mod 15k+
P: 31,615
That seems sensible Barkarlo.
Clearly we have difficulty communicating.
Good luck.
Dec 12 '06 #33

Post your reply

Sign in to post your reply or Sign up for a free account.