October 19, 2004 at 4:37 pm
Please someone help me. I created an Access form to let users submit PTO requests. On the form, they need to enter RequestDateFrom and RequestDateTo fields. Based on these fields, I will calculate numbers of hours that they are going to take. I can handle the holiday part, but not for the Saturday and Sunday part.
For ex.
DateRequestFrom 10/22/04
DateRequestTo 10/26/04
TotalHours 40 (not correct. System needs to show only 24 hours because 10/24/04 and 10/25/04 are Sat and Sun)
How can I calculate TotalHours fieldbased on DateRequestFrom and DateRequestTo fields? Thanks for any ideas.
Minh
October 19, 2004 at 8:30 pm
Yuk! If no one has anything already written, I will help you out. Presume you're planning on using VBA?
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 20, 2004 at 4:01 pm
Yes,
I am using VBA and t-sql. Do you have any clues how to do that? Thanks
Minh
October 21, 2004 at 7:14 am
This probably could be optimized, but it seems to work...
Public Function PTO(startDT, endDT) As Integer
Dim ptoHrs As Integer
Dim chkDT As Date
Dim weekendDays As Integer
Const HRS_PER_DAY As Integer = 8
ptoHrs = (DateDiff("d", startDT, endDT) + 1) * HRS_PER_DAY
chkDT = startDT
weekendDays = 0
While chkDT <> endDT
If Weekday(chkDT) = vbSaturday Or Weekday(chkDT) = vbSunday Then
weekendDays = weekendDays + 1
End If
chkDT = DateAdd("d", 1, chkDT)
Wend
ptoHrs = ptoHrs - (weekendDays * HRS_PER_DAY)
PTO = ptoHrs
End Function
October 21, 2004 at 1:05 pm
Thank you so much. It works for me.
Minh.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply