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

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi Everyone,

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

Like,

Column C.......Column DIColumn DJColumn DKColumn DL.......Column DT
3031303131
DATES......APRILMAYJUNEJULY.......MARCH
14-04-2019.......17000.......0
04-06-2019.......00270.......0
18-03-2019.......0000.......14
14-01-2019.......0000.......0
14-07-2019.......00018.......0

<tbody>
</tbody>


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

Regards,
Dhruva.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
If your formula works, then try
Code:
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
 
Last edited:
Upvote 0
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.

Column C.....Column-DIColumn-DJColumn-DKColumn-DLColumn-DMColumn-DNColumn-DOColumn-DPColumn-DQColumn-DRColumn-DSColumn-DT
303130313130313031312931
DATEAPRILMAYJUNEJULYAUGUSTSEPTOCTNOVDECJANFEBMAR
14-04-2019.....173130313130000000
14-09-2018.....30313031313001731312931
10-04-2019.....213130313130000000
10-02-2019.....30313031313000002031

<tbody>
</tbody>


Regards,
Dhruva.
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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:
Code:
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
 
Upvote 0
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:
Code:
    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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top