Based on date how to calculate total working days in particular month using vba

[GirishDhruva]

Hi Everyone,

Based on dates that are provided in column 'C' can we calculate how many days a employee had worked

Like,

[TABLE="width: 900"]
<tbody>[TR]
[TD]Column C[/TD]
[TD].......[/TD]
[TD]Column DI[/TD]
[TD]Column DJ[/TD]
[TD]Column DK[/TD]
[TD]Column DL[/TD]
[TD].......[/TD]
[TD]Column DT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD][/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]DATES[/TD]
[TD]......[/TD]
[TD]APRIL[/TD]
[TD]MAY[/TD]
[TD]JUNE[/TD]
[TD]JULY[/TD]
[TD].......[/TD]
[TD]MARCH[/TD]
[/TR]
[TR]
[TD]14-04-2019[/TD]
[TD].......[/TD]
[TD]17[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD].......[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]04-06-2019[/TD]
[TD].......[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]27[/TD]
[TD]0[/TD]
[TD].......[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]18-03-2019[/TD]
[TD].......[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD].......[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]14-01-2019[/TD]
[TD].......[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD].......[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14-07-2019[/TD]
[TD].......[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]18[/TD]
[TD].......[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


I know to do this in excel with formula =+($DK$3-DAY($C6)+1), but i am trying it in VBA

Regards,
Dhruva.

 

[Anthony47]

If your formula works, then try

WDays=Evaluate("=('Sheet_Name'!$DK$3-DAY('Sheet_Name'!$C6)+1)")

Replace Sheet_Name with the name of your sheet
The Variable WDays will hold the result of the calculation

Bye

 

[GirishDhruva]

This works, but could't we do YEAR VALIDATION for the provided data.

Year Validation means Like,
If the data is provided from LAST YEAR, and let's assume current month is September, so from the date given to till the current month it should be filled with the above values(Above values means, the rows which has the values like 30,31,30,31........)

I can take the current month from the Input field/from the calendar.

Can this be done.

[TABLE="width: 900"]
<tbody>[TR]
[TD]Column C[/TD]
[TD].....[/TD]
[TD]Column-DI[/TD]
[TD]Column-DJ[/TD]
[TD]Column-DK[/TD]
[TD]Column-DL[/TD]
[TD]Column-DM[/TD]
[TD]Column-DN[/TD]
[TD]Column-DO[/TD]
[TD]Column-DP[/TD]
[TD]Column-DQ[/TD]
[TD]Column-DR[/TD]
[TD]Column-DS[/TD]
[TD]Column-DT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]29[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]DATE[/TD]
[TD][/TD]
[TD]APRIL[/TD]
[TD]MAY[/TD]
[TD]JUNE[/TD]
[TD]JULY[/TD]
[TD]AUGUST[/TD]
[TD]SEPT[/TD]
[TD]OCT[/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[/TR]
[TR]
[TD]14-04-2019[/TD]
[TD].....[/TD]
[TD]17[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]14-09-2018[/TD]
[TD].....[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]17[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]29[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]10-04-2019[/TD]
[TD].....[/TD]
[TD]21[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]10-02-2019[/TD]
[TD].....[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]


Regards,
Dhruva.

 

[Anthony47]

The vba line mimick the formula that worked for you. If you need more then we have to di it in a different mode. However I didn't understand your needs: could you share a sample workbook and on this detail what you meen foe "data validation" and make some example of results you would like to get?

Bye

 

[GirishDhruva]

Here is my workbook:
https://www.dropbox.com/s/se24ks8s7qd3pwz/Date_MrExcel.xlsm?dl=0

From the above workbook i will ask the user to provide the current month, let's assume that current month is September and in column 'C' the date provided is '14-04-2019', so for the highlighted data it should calculate the number of days in April from 14-04-2019 to last day of month, and for the remaining months from May to September it should fill the values that are provided in 1st row. And this should be done for all the dates that are provided in column'C'.

Can this be done in excel VBA

Regards,
Dhruva.

 

[Anthony47]

Sorry I was late in responding, it was a busy day…

Let me see if I understood what you mean, referring to the published workbook and the latest description:
-the user set a month in B1
-now you would like that for each of the Dates listed in cell C3 and below, the number of calendar days be calculated, starting from the date in column C until the end of the selected month and writing the result in the corresponding Month column
If this is correct then you could use the following Macro:

Sub MonDays()
Dim TOrig As String, STMonth As Long, SCMonth As Long, ECMonth As Long
Dim I As Long, J As Long, CMon, cCol As Long
'
TOrig = "E2"        '<<< The origin of the Table
'
STMonth = Month(CDate("1-" & Range(TOrig) & "-2019"))
ECMonth = Month(CDate("1-" & Range("B1") & "-2019"))
'Scan each Date:
For J = 3 To Cells(Rows.Count, "C").End(xlUp).Row
    SCMonth = Month(Cells(J, "C").Value)
    Range(Cells(J, Range(TOrig).Column), Cells(J, Range(TOrig).Column + 13).End(xlToLeft)).ClearContents
    'Scan 12 months
    For I = 0 To 11
        CMon = Format(DateSerial(2019, SCMonth + I, 1), "mmm")                              'Get Month string
        cCol = Application.Match(CMon, Cells(Range(TOrig).Row, 1).Resize(1, 30), False)     'Get Column of the month
        If Not IsError(cCol) Then
            If I = 0 Then
                Cells(J, cCol).Value = Application.EoMonth(Cells(J, "C").Value, 0) + 1 - Cells(J, "C").Value
            Else
                Cells(J, cCol).Value = Application.EoMonth(Cells(J, "C").Value, I) - Application.EoMonth(Cells(J, "C").Value, I - 1) 'Cells(1, cCol).Value
            End If
            Cells(J, cCol).NumberFormat = "0"
            If Month(CDate("1-" & CMon & "-2019")) = ECMonth Then
                Exit For
            End If
        End If
    Next I
Next J
MsgBox ("Completed...")
End Sub

The code has to be inserted in a Standard Module of your vba, then you set the month in B2 and start Sub MonDays

A sample file is available here:
https://www.dropbox.com/s/hat6q8zgksvh4b5/byGIRISHDHRUVA-MR_Date_MrExcel_B91015.xlsm?dl=0

Note that the macro requires that the monthly headers be written in your local language, in the "mmm" format (eg: Jan, in English); in the sample file these headers are in Italian, and are highlighted in Orange (se Sheet1). Dont forget to rewrite them in your local language.

My sample file already include the code, and I personalized the Quick Access Bar adding an icon like a "spider web": that icon, pressed, will start the macro.
Try and inform about the results

Bye

 

[GirishDhruva]

Perfect, Thank you so much.

Regards,
Dhruva.

 

[GirishDhruva]

Can we make a small changes in the code, as current month is given, if the date is 1st of current month then the value in the current month should be zero.

Like the below workbook, which i have highlighted
https://www.dropbox.com/s/u0ihnwzmoin3zjr/Date_MrExcel.xlsm?dl=0

 

[Anthony47]

Can we make a small changes in the code, as current month is given, if the date is 1st of current month then the value in the current month should be zero.

Like the below workbook, which i have highlighted
https://www.dropbox.com/s/u0ihnwzmoin3zjr/Date_MrExcel.xlsm?dl=0

Hummm...
Try adding this line in this position:

    For I = 0 To 11
    If SCMonth = ECMonth And Day(Cells(J, "C").Value) = 1 Then Exit For     '<<< ADD THIS LINE
        CMon = Format(DateSerial(2019, SCMonth + I, 1), "mmm")                              'Get Month string

Bye

 

[GirishDhruva]

Thanks you so much @Anthony47