Dates and Holidays. I need direction

[Remington]

Back again, thirsting for knowlege. :)

http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.

I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.

My Question now, is How do I figure in weekends? And Possibly Holidays.

i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.

I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.

Thank you

[ADezii]

Back again, thirsting for knowlege. :)

http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.

I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.

My Question now, is How do I figure in weekends? And Possibly Holidays.

i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.

I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.

Thank you

'Unless you modify the firstdayofweek Parameter to this Function:
'To see if a Date falls on a Saturday or Sunday:
If Weekday([YourDate]) = 7 Or If Weekday([YourDate]) = 1 Then
    'the Date is on a Saturday (7) or Sunday (1)
    'process code here
End If
 
'Holidays would have to be pre-determined and filtered out.

[NeoPa]

Just to fill in with some more explanation :
Holidays is one of those often asked questions that seem to some, to be obviously very easy. Some need to be disabused! It is a very complicated area as it does not fit very well into the concepts of an RDBMS. There are threads in TSDN which go into this in more detail and do resolve the issue (mostly), but they are hard work and require extra maintenance.
Another similarly tough question that some feel should be very easy, is numbering lines returned from a query. Again, there are threads that explain how, but unless you have a hardy constitution, you don't want to go there.

[Remington]

'Unless you modify the firstdayofweek Parameter to this Function:
'To see if a Date falls on a Saturday or Sunday:
If Weekday([YourDate]) = 7 Or If Weekday([YourDate]) = 1 Then
    'the Date is on a Saturday (7) or Sunday (1)
    'process code here
End If
 
'Holidays would have to be pre-determined and filtered out.

So, where you have the "YourDate", I would switch with "LeaveDate" and "ReturnDate" Those being the fields that hold the dates in my app.

If Weekday([LeaveDate]) = 7 Or If Weekday([LeaveDate]) = 1Then
???? Week = 5 days? Then I would have to send 5 days plus any other days from the following week, to my "DaysGone" field. Something isnt making sense....could you by chance give me a little more detail?

[Remington]

I have spoke with the HR department, about the holidays and they said that, they could manually do everything else, but if I could get the weekends to subtract from my total number of days. Like every 7 days, then subtract 2 days or something. Either way, it makes my task a little less daunting knowing that I don't have to figure out the holidayz

Thx

[NeoPa]

Have a look at ADezii's post again and see if you can work it out.
I'm going out now but will be back later. If you post that you still can't see why it's right then I will give a fuller explanation for you when I get back.

[MMcCarthy]

So, where you have the "YourDate", I would switch with "LeaveDate" and "ReturnDate" Those being the fields that hold the dates in my app.

If Weekday([LeaveDate]) = 7 Or If Weekday([LeaveDate]) = 1Then
???? Week = 5 days? Then I would have to send 5 days plus any other days from the following week, to my "DaysGone" field. Something isnt making sense....could you by chance give me a little more detail?

This is simply a way of checking if a date is a saturday or a sunday. You would start at "LeaveDate" and check each date up to "ReturnDate" and add 1 for each date that wasn't a weekend day.

Mary

[Killer42]

I thought DateDiff had a weekdays-only option. (Or am I thinking of DateAdd...)

[NeoPa]

I've tried the 'Weekdays' option in Access2K (I forget which function - DateAdd() I think) and it's buggy. I ended up creating my own function to do it :(

[Killer42]

I've tried the 'Weekdays' option in Access2K (I forget which function - DateAdd() I think) and it's buggy. I ended up creating my own function to do it :(

Oops!

Definitely another one to add to the tips and tricks.

[NeoPa]

Oops!

Definitely another one to add to the tips and tricks.

If you like.
I'll get posting tomorrow probably.

[tocangah]

Hi guys

I encounter the same pain at the moment in my MS Access. Can I know what is the VBA or Expression codes? Mine is similar; I have Date1 and Date2. I want to have a separate column which would count the difference of both dates (in terms of weekdays and if possible, exclude out certain days like public holidays, which would be added in manually). I’ve tried DateDiff but it didn’t work for me. At last, I used Date2-Date1 as temporary resort. Is there any other way to do this, at least counting only the weekdays?

Thanks.

Regards
Anthony

Back again, thirsting for knowlege. :)

http://www.thescripts.com/forum/show...76#post2314976
Was my last thread. I am still working on the same project, but a different stage.

I now have my form, subtracting the leave date, and return date. The result is then sent to my "DaysGone" box and then its value is multiplied by 8 (8 hours in a workday) and then send to "HoursGone" The first step was getting it all to work, and it works great now, thanks to help from the community, mainly Mary.

My Question now, is How do I figure in weekends? And Possibly Holidays.

i.e. A employee leaves on wednesday and returns the followoing wednesday, he would be missing 7 days. However, since weekends do not count, the employee has only missed 5 days of work.

I am not expecting anyone to hand me the code (though it would help ;) ) If there is a good place to start researching this, just point me in the right direction, is all I ask.

Thank you

[NeoPa]

Oops!

Definitely another one to add to the tips and tricks.

If you like.
I'll get posting tomorrow probably.

Here it is (Function to Move (Forwards or Backwards) Through Weekdays).

[Remington]

'MoveWD moves datThis on by the intInc weekdays.
Public Function MoveWD(datThis As Date, intInc As Integer) As Date
    MoveWD = datThis
    For intInc = intInc To Sgn(intInc) Step -Sgn(intInc)
        MoveWD = MoveWD + Sgn(intInc)
        Do While (Weekday(MoveWD) Mod 7) < 2
            MoveWD = MoveWD + Sgn(intInc)
        Loop
    Next intInc
End Function

Ok, i think i have the process of this down in my head. But a few noob questions.

1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?

2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?

I understand what the code is doing, i guess im just missing how to integrate it into my project.

Thanks yet again :)

[MMcCarthy]

1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?

This is just a function name. You use this name to call the function elsewhere

2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?

To call this function you would simply use ...


Me.DateFieldName = MoveWD(<Date Parameter>)

Date Parameter is whatever the variable name is that the calendar stores the date in.

[NeoPa]

1. The function MoveWD(datThis As Date....etc.
What is MoveWD? Is it a form? is it a button?

MoveWD, as Mary says in her post, is the function itself.

2. Do I tie this function into the Calendar code, or into the field that the calendar results are put into?

Me.DateFieldName = MoveWD(<Date Parameter>)

Date Parameter is whatever the variable name is that the calendar stores the date in.

Generally you would create a new module and place it in there (unless you already had a general purpose module for such routines). Call it from your code.
Here are some more detailed steps to help :

1. From the Access window hit Alt-F11 to open and switch to the VBA window.
2. Select Insert / Module.
3. In the newly created module window paste in the code.
4. You can now call it from just about anywhere in your project - SQL; Query; VBA Code; etc.
5. The calling code is as Mary states, except that you also need to supply an intInc parameter. This says how many weekdays forwards (+) or backwards (-) you want to go.

[Remington]

What do you mean by "How many days you want to go forward (+)"?

The dates that I am working with are selected in two fields. One being the leave date and the other return date. The amount of days I want go forward is based on the difference between those two fields.

Is there another bit of code i need to stick in there to patch it all together?

[NeoPa]

What do you mean by "How many days you want to go forward (+)"?

This is saying that you put in a positive number for moving the date onwards (forwards). Conversely, if you want a number of weekdays before a given date you use a negative number of days.

The dates that I am working with are selected in two fields. One being the leave date and the other return date. The amount of days I want go forward is based on the difference between those two fields.

Is there another bit of code i need to stick in there to patch it all together?

Are you trying to say that you want to find the number of weekdays between two dates rather than add (or subtract) weekdays to (from) a given date?