• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dates Between Two Dates

tbarnard93

New Member
I have a table with two columns which shows the start and end date of an operation (Columns A & B of the attached). I'm looking for either a formula or VBA to get a daily profile which shows when the system is operational (See columns D & E for an example). Essentially where I have manually entered a 1 to represent the system as active or 0 to represent the system as inactive (column E), I want the formula/VBA to do this for me.
 

Attachments

  • Book1.xlsx
    11.3 KB · Views: 5
Unfortunately that doesn't work for me - it only gives a 1 for the first value. See attached
 

Attachments

  • Copy of Book1.xlsx
    11.8 KB · Views: 1
True. That is because the other dates do not fall between the start and end date. This is how I interpreted your data. If this is not a correct assumption, then explain to us (which you did not do in your OP) what your criteria for 1 and criteria for 0 are based upon the information provided.
 
Reread your post and try this:

Code:
Option Explicit

Sub Operational()
    Dim i As Long, j As Long, lr As Long, lr2 As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    lr2 = Range("D" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr2
        For j = 2 To lr
            If Range("D" & i) >= Range("A" & j) And Range("D" & i) <= Range("B" & j) Then
                Range("E" & i) = 1
            End If
        Next j
    Next i
    For i = 2 To lr2
    If Range("E" & i) = "" Then Range("E" & i) = 0
    Next i
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub
 
Back
Top