Writing a Macro to determine the amount of work days between two dates.

[matthew_dubbels]

I am trying to write a macro that will compare 2 cells, then will take the dates corresponding to those cells and tell me the amount of week days between the 2 dates.

The part I'm having trouble with is the IF i wrote

Sub DateCalculation()

'
' DateCalculation Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'

'Inserting Columns I
Columns(9).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'In cell I1 put the text "Number of Work Days"
Range("I1").FormulaR1C1 = "Number of Work Days"

'Creating variable and setting as a range
Dim MyRange As Range
Dim MyCell As Range

'Creates Date variables firstDate and secondDate, weekDays is created as an integer
Dim firstDate As Date, secondDate As Date, weekDays As Integer

'Creates Lastrow Function to be the equivalent of a 'end' + 'down arrow' operation
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row

'MyRange is equal to A2-A(lastrow)
Set MyRange = Range(Cells(2, 1), Cells(Lastrow, 1))

'loop to continue down the column A one at a time until the lastrow
For Each MyCell In MyRange

'Broken Part
If MyCell.Value = Cells(MyCell - 1, 1).Value Then

firstDate = Cells(MyCell, 8)
secondDate = Cells(MyCell - 1, 8)
weekDays = DateDiff("w", firstDate, secondDate)

Cells(MyCell, 9) = weekDays

Else

Cells(MyCell, 9) = "0"

End If

'Goes to next cell down
Next MyCell


End Sub

 

[matthew_dubbels]

What this should do is go through column A which will have some text such as I-01452 and compare it to the one below it, if they match then the dates difference, dates are in column H, should be calculated in column I. If they don’t match then just continue on and skip that lines calulation.

 

[MickG]

One thing "Mycell" is a range Object
The code lines below and similar should possibly altered as shown
From this:-

firstDate = Cells(MyCell, 8)

To this:-

firstDate = Cells(MyCell.row, 8)

 

[Mentor82]

Hi,
To calculate work days between two dates yoy can use built-in worksheet function. Called workday which has three parameters, start date, end date, optional array or range of dates which are considered as holidays. First two parameters aare required and calculates no of days between two dates skipping weekends (Saturdaya and Sundays) but third parameter is optional and it's required to be filled in if you want to subtract also holidays dates which are during the week(Mon-Fri)

  NoWorkDays=application.worksheetfunction.workday(startdate, enddate, arr_holidaysdates)

 

[matthew_dubbels]

One thing "Mycell" is a range Object
The code lines below and similar should possibly altered as shown
From this:-

firstDate = Cells(MyCell, 8)

To this:-

firstDate = Cells(MyCell.row, 8)

Thank you! I tried doing this at got it to count the total amount of days between, still working on trying to make it count only work days.

If MyCell.Value = Cells(MyCell.Row + 1, 1).Value Then
        
            firstDate = Cells(MyCell.Row, 8)
            secondDate = Cells(MyCell.Row + 1, 8)
            weekDays = DateDiff("d", secondDate, firstDate)
            
            Cells(MyCell.Row, 9) = weekDays
           
        Else
            
            Cells(MyCell.Row, 9) = "First Recored MSG"
    
        End If