Forum Discussion

farhansaleem's avatar
farhansaleem
Copper Contributor
May 28, 2019
Solved

Data Extraction

Hi, I have some data arranged in Year, Month, Day format. I want to extract the common numbers of days (respective to month and year) from the 4 data sets.  I have also attached the excel fil...
excel
  • Berndvbatanker's avatar
    Berndvbatanker
    May 28, 2019

    farhansaleem 

    Hi, i hope that I have understood the task. You want to check if a date is on monday to friday?

    I write an userdefined function, see attached file, too.

     

    Function commonDays(strYear As String, strMonth As String, strDay) As Boolean
    Dim dteDate As Date

    dteDate = DateSerial(strYear, strMonth, strDay)

    If Application.WorksheetFunction.Weekday(dteDate, vbMonday) < 6 Then
    commonDays = True

    Else

    commonDays = False

    End If

    End Function

    Regards from germany

    Bernd

    http://www.vba-Tanker.com

     

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
May 29, 2019

farhansaleem 

This was a solution obtained with the dynamic array version of Excel to produce a list.  It is possible to do it in standard versions of Excel.   The count only requires COUNTIFS though.

Berndvbatanker 

I am sure an efficient VBA solution is available for this class of problem using the scripting dictionary to identify duplicates.

 

Resources