Date Challenges x 2 (1 Viewer)

[andy_dyer]

Hi everyone - I've got a couple of interesting challenges that i don't know where to begin...

I have a WorkingDay module (found elsewhere on this forum ) that is working well for me elsewhere that feeds off a holiday table etc

(1) Calculated Target1 Date

With this challenge I have a start date field and a priority field - I need to be able to, based on the priority, set the target date in a calculated date field.

Priority 1 = 1 working day
Priority 2 = 3 working days
Priority 3 = 5 working days

i.e. if the start date was 25/01/2013 and it was a priority 1 then I want it to take the startdate field, calculate the target date based on 1 working day = 28/01/2013

How can I calculate this date in the future based on these requirements?

(2) Calculated Target2 Date

With this challenge I need to be able to take the previously calculated Target1 Date and calculate the next target date.

For this it can only be a Tuesday or a Thursday, so I need to be able to take Target1 Date and then calculate the date of the next Tuesday or Thursday.

i.e. if Target1 Date was Friday 25/01/2013 I want Target2 Date to calculate the next Tuesday or Thursday will be Tuesday 29/01/2013 etc

I've posted below my current module that helps me work out my working days in case any of this needs to be modified or added too...

Option Compare Database
Option Explicit

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function

Public Function Weekdays(ByRef startDate As Date, _
    ByRef endDate As Date _
    ) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

Thanks to anyone who can help with these - I am really stuck...

 

[andy_dyer]

Ok quick update... I've sorted (1) but any ideas on (2)?

I'm still stumped...

?????

 

[andy_dyer]

I'm still stumped on

(2) Calculated Target2 Date

With this challenge I need to be able to take the previously calculated Target1 Date and calculate the next target date.

For this it can only be a Tuesday or a Thursday, so I need to be able to take Target1 Date and then calculate the date of the next Tuesday or Thursday.

i.e. if Target1 Date was Friday 25/01/2013 I want Target2 Date to calculate the next Tuesday or Thursday will be Tuesday 29/01/2013 etc

I've searched all over the web and I can't find any code that allows me to calculate the date of the next Tues or Thurs whichever is soonest...

In real terms my code wants to say:

if the day of the week of the field target_fixdate is a thursday, friday or a monday i want it to give me the next tuesday date, if the day of the week of the target_fixdate is a tuesday or a wednesday i want it to give me the next thursday date.

Does that make sense?

Thanks so much for your time in advance.

 

[Brianwarnock]

Can't you use a select case construct


Select case weekday(target1)

Case 4. Ie Wednesday
Target2 = dateadd("d",1,target1)
Case Well you get the idea

Brian

 

[andy_dyer]

Hi Brian,

Thanks for responding - yes I have found some Select Case code (but I have never used it before in any of my simple databases I've built...) that is kind of like that... but I can't work out;

1 - how I get the "day" from a date i.e. 28/01/2013 = Monday
2 - how to get it to look for either a Tues or a Thurs
3 - how I get it check if it is a Tues or Thurs that is next
4 - how I get the date back out for the Tues/Thurs

Can you help me with any of the above?

 

[Brianwarnock]

Let's see if the old rust can be polished up.

Assuming that you have never changed any defaults then Sunday is Day 1
thus x = Weekday(Date()) for today 28 Jan 2013 would return 2

As I undestand it Target1 has a date that you want to use to get the next Tuesday or Thursday from, Tuesday if Thursday 5 ,Friday 6 Or Monday 2
Thursday if Tuesday 3 or Wednesday 4

So nothing clever just

Select Case weekday(Target1)
Case 5
Target2 = dateadd("d",5,target1)
Case 6
Target2 = dateadd("d",4,target1)
Case 2
Target2 = dateadd("d",1,target1)
Case 3
Target2 = dateadd("d",2,target1)
case 4
target2 =dateadd("d",1,target1)
end select

Brian

 

[andy_dyer]

Thanks Brian - I've left the office now but will give that a try tomorrow - I'll let you know how I get on.

Just wanted to thank you quickly for your time while I knew you were online!

 

[andy_dyer]

Just quickly how would I call that either in the form or via the query that sits behind the form?

 

[Brianwarnock]

Hi
Thanks just read this as as it was after 5 and all the workers had gone home I decided to test it, thankfully it worked, so happy coding.

Brian

 

[andy_dyer]

All working! many thanks

 

[andy_dyer]

I'm thinking I need to use this within the main query that is the source for the form instead of calculated within the form itself...

I reached this conclusion after needing to scroll through all 1000 records to populate the detail in the query... I figure this isn't ideal and should be calculated on the fly from the query and then passed through to the form...

How would I either convert this to a function and call it from the query or convert to a long and complicated nested IIf...

Any ideas?

 

[Brianwarnock]

I assumed that it was a function, don't know how else to use it, but lets start from scratch.
I start my functions with an f

Function fTarget2(target1 as date) as date

Select Case weekday(Target1)
Case 5
fTarget2 = dateadd("d",5,target1)
Case 6
fTarget2 = dateadd("d",4,target1)
Case 2
fTarget2 = dateadd("d",1,target1)
Case 3
fTarget2 = dateadd("d",2,target1)
case 4
ftarget2 =dateadd("d",1,target1)
end select

end function

This is copied into a module which must have a different name, if i only have a couple of functions I just use the default module1

to use in say a query create a field
Target2:ftarget2(target1)

hope this helps

Brian

 

[andy_dyer]

Thanks Brian - sorted (again!)

 

[Brianwarnock]

Just happy to be useful.