Vba to distribute days across multiple years

[motherindia]

[TABLE="width: 825"]
<colgroup><col><col><col><col span="8"></colgroup><tbody>[TR]
[TD="colspan: 2"]Before Running macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"]The sheeet1 contains following data. I need number of days between 1-4-2014 40 31-3-2019 which should be displayed in shet2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust ID[/TD]
[TD]St Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2012[/TD]
[TD="align: right"]30-04-2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD="align: right"]30-04-2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]After running macro sheet2 sould have follong out put[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 5"]Years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]01-04-2014[/TD]
[TD="align: right"]01-04-2015[/TD]
[TD="align: right"]01-04-2016[/TD]
[TD="align: right"]01-04-2017[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust ID[/TD]
[TD="align: right"]31-03-2015[/TD]
[TD="align: right"]31-03-2016[/TD]
[TD="align: right"]31-03-2017[/TD]
[TD="align: right"]31-03-2018[/TD]
[TD="align: right"]31-03-2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]365[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Regards,
motherindia

 

[onlyadrafter]

Hello.

There doesn't seem to be any logic between sheet1 and sheet2. Can you clarify. The dates are totally different. Or am I missing something?

 

[motherindia]

Thanks for quick responses.
The example given above only to get 5 years between 1st apr to 31st March.

Regards,
motherindia

 

[motherindia]

[TABLE="width: 889"]
<colgroup><col><col><col><col span="8"><col></colgroup><tbody>[TR]
[TD]Hi Team,[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]I need to capture number of days that falls between start and end date for specific 5 years. For example I need to capture number of days that falls[/TD]
[/TR]
[TR]
[TD="colspan: 7"]between 1st Apr 2014 to 31st Mar 2019 for cust ID 1 in the following example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust ID[/TD]
[TD]St Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2012[/TD]
[TD="align: right"]30-04-2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD="align: right"]30-04-2020[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]After running macro sheet2 should have following out put[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust ID[/TD]
[TD="colspan: 5"]Years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]01-04-2014[/TD]
[TD="align: right"]01-04-2015[/TD]
[TD="align: right"]01-04-2016[/TD]
[TD="align: right"]01-04-2017[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="align: right"]31-03-2015[/TD]
[TD="align: right"]31-03-2016[/TD]
[TD="align: right"]31-03-2017[/TD]
[TD="align: right"]31-03-2018[/TD]
[TD="align: right"]31-03-2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD="colspan: 5"]Nof Days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]365[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Regards,
motherindia

 

[onlyadrafter]

Hello,

Can get the dates, as per your example.

Sub years()
    MY_CUST = Range("A2").Value
    MY_END = Year(Range("C3").Value) - 1
    MY_START = MY_END - 5
    With Sheets("Sheet2")
        .Range("A1").Value = "Cust ID"
        .Range("A2").Value = MY_CUST
        .Range("B1").Value = "Years"
        For MY_COLS = 2 To 6
            .Cells(2, MY_COLS).Value = DateValue("1/4/" & MY_START)
            .Cells(3, MY_COLS).Value = DateValue("31/3/" & MY_START + 1)
            MY_START = MY_START + 1
        Next MY_COLS
    End With
End Sub

Not sure how you get the Nof days though. There doesn't seem to be any logic to the numbers in the sample.

 

[motherindia]

Hello Sir,
Thanks a lot for quick responses;

Problem :
[TABLE="width: 424"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Cust ID[/TD]
[TD]St Date[/TD]
[TD]End Date[/TD]
[TD]Place[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2012[/TD]
[TD="align: right"]30-04-2016[/TD]
[TD]Delhi[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]01-04-2018[/TD]
[TD="align: right"]30-04-2020[/TD]
[TD]Mumbai[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 1020"]
<colgroup><col><col><col><col span="8"><col span="3"></colgroup><tbody>[TR]
[TD="colspan: 13"]Let me give another example . In the above example I have added another column called Place so that it is easy for understanding the requirement. [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 12"]Suppose if I want to know how many days I was in Delhi and in Mumbai between year 2014 to 2020. However, the days should be calculated between Apr to March.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]That is the number of days in Delhi for the year 2014-15 ( 01.04.2014 to 31.03.2015) is 365[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]That is the number of days in Delhi for the year 2014-15 ( 01.04.2015 to 31.03.2016) is 366[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]That is the number of days in Delhi for the year 2014-15 ( 01.04.2016 to 31.03.2017) is 30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]That is the number of days in Delhi for the year 2014-15 ( 01.04.2017 to 31.03.2018) is 0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]That is the number of days in Mumbai for the year 2014-15 ( 01.04.2014 to 31.03.2015) is 365[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Hence the out put should be as follows for the number of days[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 11"]The macro can prompt for input the date range to capture number of days (Between 1st Apr to 31st Mar) eg: between 2014 to 2020.[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cust ID[/TD]
[TD]2014-15[/TD]
[TD]2015-16[/TD]
[TD]2016-17[/TD]
[TD]2017-18[/TD]
[TD]2018-19[/TD]
[TD]2019-20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]366[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Regards,
motherindia