Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Conditional Formatting...Weekdays Only?

    Hello,

    I'm trying to conditionally format a field. What I want is if the date difference is 1 or less, it is green, 2 is yellow, 3 or more is red. The below works for what I need, except it counts weekends:

    Condition 1
    Expression is: IIf([Status]="Full",(Date()-[DateDropped])=2,([DateCompleted]-[DateDropped])=2)
    Format = Yellow

    Condition 2
    Expression Is: IIf([Status]="Full",(Date()-[DateDropped])>=3,([DateCompleted]-[DateDropped])>=3)
    Format = Red

    Condition 3
    Expression Is: IIf([Status]="Full",(Date()-[DateDropped])<=1,([DateCompleted]-[DateDropped])<=1)
    Format = Green


    I have been searching all day...Does anyone know of a condusive way to get this to not count weekends?

    Thanks,
    Chris

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Is this in a form or report? Are Status, DateDropped and DateCompleted displayed in textboxes? If so, what are the text boxes named? If not, how are they displayed?

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by rogue View Post
    Is this in a form or report? Are Status, DateDropped and DateCompleted displayed in textboxes? If so, what are the text boxes named? If not, how are they displayed?
    I am terrible at naming my fields.

    This is in a form, and the fields are:
    Status = Combo22
    DateDropped = Text16
    DateCompleted = Text17

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Found a solution, here is what I did:

    I found this code, changed it to fit my needs and placed it in a module:

    Code:
    Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
    '-- Return the number of WorkingDays between StartDate and EndDate
    On Error GoTo err_workingDays
    
    Dim intCount As Integer
    
    If IsDate(StartDate) And IsDate(EndDate) Then
       If EndDate >= StartDate Then
    
          intCount = 0
          Do While StartDate < EndDate
             StartDate = StartDate + 1
    '         If Weekday(StartDate, vbMonday) <= 5 Then
          '-- Use the following code if you have a "Holiday" table
             If Weekday(StartDate, vbMonday) <= 5 And _
                IsNull(DLookup("[Holiday]", "tbl_Holidays", _
                "[HolDate] = " & Format(StartDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
    
                intCount = intCount + 1
             End If
          Loop
          WorkingDays = intCount
       Else
          WorkingDays = -1  '-- To show an error
       End If
    Else
       WorkingDays = -1  '-- To show an error
    End If
    
    exit_workingDays:
       Exit Function
    
    err_workingDays:
       MsgBox "Error No:    " & Err.Number & vbCr & _
       "Description: " & Err.Description
       Resume exit_workingDays
    
    End Function
    Then I created a holiday table:
    tbl_Holidays
    Fields: Holiday, HolDate

    I then created a new field in the form which is used for # of elapsed days. The field is called [WorkDays].
    The data is =IIf([Status]="Full",workingdays([DateDropped],Date()),workingdays([DateDropped],[DateCompleted]))

    Then I set the Conditional Formatting to:
    Condition 1
    [WorkDays]=3
    Condition: Yellow

    Condition 2
    [WorkDays]>=4
    Condition: Red

    Condition 3
    [WorkDays]<=2
    Condition: Green


    This works perfectly for the question I had asked. It may not be the best or prettiest solution, but it works.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •