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.

 

[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.

Sorry to ask for modification again,

Can the below clarifications be resolved.

• Assume that if the current month is September and the Effective date is provided from April, then it should calculate only from April to August but it shouldn't calculate for September
• When the current month should be calculated means, when the Effective date is provided after 2nd of current month(i.e, 02-09-2019 then it should calculate from 2nd to end of the month)

Like the provided workbook below,
https://www.dropbox.com/s/ua24yayyuh25imu/Date_MrExcel.xlsm?dl=0

Regards,
Dhruva

 

[Anthony47]

Sorry to ask for modification again,

Can the below clarifications be resolved.

• Assume that if the current month is September and the Effective date is provided from April, then it should calculate only from April to August but it shouldn't calculate for September
• When the current month should be calculated means, when the Effective date is provided after 2nd of current month(i.e, 02-09-2019 then it should calculate from 2nd to end of the month)

Like the provided workbook below,
https://www.dropbox.com/s/ua24yayyuh25imu/Date_MrExcel.xlsm?dl=0

Regards,
Dhruva

Not sure I understood your first point, but try modifying as follows:

            If Month(CDate("1-" & CMon & "-2019")) >= (ECMonth - 1) Then   '<< MODIFIED LINE
                Exit For

Surely I didn't understand your second point: could you give, on the worksheet that you shared, a couple of examples? I.e. which are the current results, what you would like to see and why?

Bye

 

[GirishDhruva]

Sorry for the late reply.

I have already shared the workbook in which i have provided example(which is highlighted).

assume if the current month is October, if the effective date is provided from 02-10-2019 - 31-10-2019 only then it should calculate for the remaining days.
If they provide 1st day of provided month or if they provide any other month then the value in the current month should be "0" or blank.

Regards,
Dhruva.

 

[Anthony47]

Having already examined the workbook you shared, I can only confirm that I didn't understood your request; non I even am not sure whether you asked for 1 modification or 2.
Let's start for your latest workbook, and let's use the macro that we defined in October.
At this point, with the "effective dates" you shared and using OCT as "current mont" (cell B1), try to explain which results do not match your new requirements: what is the current result, what you would like to see instead of that, how this new value is calculated. Two or three examples could be sufficient.

Bye

 

[GirishDhruva]

please check with the highlighted rows in the below workbook.

It is only calculating for the particular month.

https://www.dropbox.com/s/0q0oy16qpyrws8j/S_R.xlsx?dl=0

Regards,
Dhruva.

 

[Anthony47]

You should have realized that I am not as smart as I pretend to be...
Could you please make clear which macro are you using (it is not in your xlsx file), which results you obtain (those that the macro reports) and which results you would like to see, instead?

Bye

 

[GirishDhruva]

Hi @Anthony47

Sorry and here is my workbook which has macros in it.

https://www.dropbox.com/s/02248zpvac2ezgo/S_R.xlsm?dl=0

from the above workbook check with the highlighted lines,

• year is different in effective date so based on that can we change the calendar dates.
• based on those year(from effective date) can we calculate till the current month(OCT) with the current calendar year(from system calendar)

Regards,
Dhruva.

 

[GirishDhruva]

@Anthony47 couldn't we do based on year.

Like, if the Effective date provided is "10-10-2018" and current month is "Feb-2019" then
it only calculates for the "October month 2018" but it should calculate till the current month provided.

Is their any possible ways to calculate from effective month to the current month with based on year also.

Regards,
Dhruva.