Calculate number of workdays since a date

[BravoBravoAu]

Hi experts - I'm looking for a macro to calculate the number of work days since a date (when a document was signed). Below is columns F and G from the workbook.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Document signed date[/TD]
[TD]Workdays since[/TD]
[/TR]
[TR]
[TD]01-January-2018[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15-February-2018[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Now, I know that this can be fairly easily done with a NETWORK days formula, but here is the kicker, I want to be able to assign the macro to a shape at the top of the column (located in the header of column G) so that the 'workdays since' (G2) calculates the workdays between 01-January-2018 (F2) and todays date, when the cell is selected and the macro button is pressed.

Obviously if there are ten dates listed (F2:F11) that I want calculated in the corresponding 'workdays since' column (G2:G11), this would be identified by G2:G11 being selected and the macro button pressed.

Help!! Thanks in advance.

 

[Tetra201]

See if this works for you:

Sub Test()
    Dim cll As Range
    For Each cll In Selection
        If Left(cll.Address, 2) = "$G" Then
            If IsDate(cll.Offset(, -1)) Then cll = WorksheetFunction.NetworkDays(cll.Offset(, -1), Date)
        End If
    Next cll
End Sub

 

[BravoBravoAu]

Thanks @Tetra201 - this appears to work as I'd hoped. Much appreciated