VBA - Date and time

[Caramelsky88]

Hi everyone!

I have a VBA question that is stumping me. I need a formula that looks at the date and based on how many days there are in the month divides the number next to it and allocates the rest of the amount to the following month. like for example:

If these are the date ranges [TABLE="width: 500"]
<tbody>[TR]
[TD]12/21/2015[/TD]
[TD]1/22/2016[/TD]
[TD]531[/TD]
[/TR]
[TR]
[TD]1/22/2016[/TD]
[TD]2/22/2016[/TD]
[TD]416[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would need to find out how much of the 531 and 416 are apart of Jan 2016. Can someone help out with this??

Thanks in advance!

 

[alansidman]

Here is a non-vba solution with helper columns.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
1	Start	End	Value	Current Month	Next Month	Days in Month	Days to End of Month
2	12/21/2015	1/22/2016	531	171.2903226	359.709677	31	10
3	1/22/2016	2/22/2016	416	120.7741935	295.225806	31	9
4
Sheet1

Cell Formulas
Range	Formula
D2	=(G2/F2)*C2
D3	=(G3/F3)*C3
E2	=C2-D2
E3	=C3-D3
F2	=DATEDIF(EOMONTH(A2,-1),EOMONTH(A2,0),"D")
F3	=DATEDIF(EOMONTH(A3,-1),EOMONTH(A3,0),"D")
G2	=DATEDIF(A2,EOMONTH(A2,0),"D")
G3	=DATEDIF(A3,EOMONTH(A3,0),"D")

[TABLE="width: 85%"]
<tbody></tbody>[/TABLE]

 

[Caramelsky88]

This is actually a big help! Thanks a lot!