开发者

Advanced DateDiff using MSExcel

开发者 https://www.devze.com 2023-02-06 05:52 出处:网络
I have an Excel spreadsheet listing job shifts... four columns that are important for our purposes: Start Date

I have an Excel spreadsheet listing job shifts... four columns that are important for our purposes:

Given this data, I need to abstract the number of hours for each shift that fall between 7am and 7pm.

A few things to keep in mind...

  • There is no theoretical limit to the length of a shift. It could run anywhere from 1 hour to 3 days.
  • I'm open to solutions in C#, VB, PHP, or even an advanced Excel function... anything to get to the solution.

  • VBA , not taking much account of minutes. I do not know if you need minutes.

    Dim i As Integer
    Dim ShiftRange As Range
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim HourCount As Long
    Dim MinCount As Long
    
    Set ShiftRange = Sheet1.UsedRange
    
    For i = 2 To ShiftRange.Rows.Count
        HourCount = 0
        dteStart = CDate(Cells(i, 1) + Cells(i, 2))
        dteEnd = CDate(Cells(i, 3) + Cells(i, 4))
    
        Do While dteStart <= dteEnd
            If Hour(dteStart) >= 7 And Format(dteStart, "hh:mm") <= #7:00:00 PM# Then
                HourCount = HourCount + 1
            End If
    
            dteStart = DateAdd("h", 1, dteStart)
        Loop
    
        MinCount = HourCount * 60
    
        ''Minutes
        If CDate(Cells(i, 2)) >= #7:00:00 AM# And CDate(Cells(i, 2)) <= #7:00:00 PM# Then
             MinCount = MinCount - Minute(CDate(Cells(i, 2)))
        End If
    
        If CDate(Cells(i, 4)) >= #7:00:00 AM# And CDate(Cells(i, 4)) <= #7:00:00 PM# Then
            MinCount = MinCount + Minute(CDate(Cells(i, 4)))
        End If
    
        Cells(i, 6) = MinCount 
    Next
    

    This assumes that A, B, C and D are the columns that contain dates and times and that colum F is empty.


    Thank you Remou Very nice and workable.

    I made a change to allow for parts of hours

    Sub DayHours()
    Dim i As Integer
    Dim ShiftRange As Range
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim MinCount As Double
    
    Set ShiftRange = Sheet1.UsedRange
    
    For i = 3 To ShiftRange.Rows.Count
    MinCount = 0
    dteStart = CDate(Cells(i, 3) + Cells(i, 4))
    dteEnd = CDate(Cells(i, 5) + Cells(i, 6))
    
    Do While dteStart <= dteEnd
        If Format(dteStart, "hh:mm") >= #7:00:00 AM# And Format(dteStart, "hh:mm") < #7:00:00 PM# Then
            MinCount = MinCount + 1
        End If
    
        dteStart = DateAdd("n", 1, dteStart)
    Loop
    Cells(i, 10) = MinCount / 60
    Next
    End Sub
    
    0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消