Need help with finding the date difference in months between 2 different cells if the day is less than 15

[cdelappe]

Hi-

Need some help.

I am trying to design a depreciation register and am having problems with the datedif() formula. Wondering is someone Know of a way to calculate between dates if the day in the purchase date is less than or equal to 15, then start counting from purchase date month. If it is not than start calculating from the month after the purchase date?


Formula is as follows...
06/30/2012 07/01/2011

	A	B	C	D	E	F	G	H	I	J	K
1	06/30/2012	07/01/2011
2	Tag No	Purchase Date	useful life in yrs	est end date	months in use	purch cost	prior yrs accum	current yrs dep	annual net book	monthly dep	total deprec
3	ps-0572	05/21/2012	4	05/20/2016	1	2547.88	-	636.97	2494.80	53.80	53.80
4	ps-0572	05/21/2012	4			2547.88	$0.00	53.08	2494.80	53.80	53.80

<tbody>
</tbody>

The Third Row is what my calculations are coming up with, 4th Row is what they should be.

Formulas:

Column D:

• =IF(C3<=0," ",B3+(C3*365))

Column E:

• =IF(D3<=$A$1,"",ROUND(DATEDIF(B3,$A$1,"m"),0))

Column F:

• Is a Fixed column

Column G:

• =IF(E3<12,0,IF(AND(D3<$A$1,H3=0),F3,(K3-H3)))

Column H:

• =IF(AND(B3<$A$1,B3>$B$2),ROUND(DATEDIF(B3,$A$1,"YM")-DATEDID($B$1,B3,"YM"),0)*J3,IF(D3<$A$1,0,F3/C3))

​Column I:

• =F3-K3

Column J:

• =IFERROR(F3/C3/12," ")

Column K:

• =IF(D3<$A$1,F3,ROUND(DATEDIF(B3,$A$1,"M"),0)*J3)

Thank you in Advance for your assistance in this problem.

 

[Snakehips]

cdelappe,

Welcome to MrExcel.

Perhaps this will help?

Either use a 'helper' columnl (New column c) and make appropriate references to C in your formulas, rather than the current B references.

OR incorporate the formula C3 directly, in your other formulas. Eg as in E4.
Excel 2007

Excel 2007
	B	C	D	E
2	Purchase Date		useful life in yrs	est end date
3	15/05/2012	15/05/2012	4	14/05/2016
4	15/05/2012		4	14/05/2016
Sheet2

	B	C	D	E
2	Purchase Date		useful life in yrs	est end date
3	21/05/2012	21/06/2012	4	20/06/2016
4	21/05/2012		4	20/06/2016

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula C3 =IF(DAY(B3)<16,B3,DATEVALUE(DAY(B3)&"-"&MONTH(B3)+1&"-"&YEAR(B3))) E3 =IF(D3<=0," ",C3+(D3*365)) E4 =IF(D4<=0," ",IF(DAY(B4)<16,B4,DATEVALUE(DAY(B4)&"-"&MONTH(B4)+1&"-"&YEAR(B4)))+(D4*365)) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Hope that helps.

 

[cdelappe]

cdelappe,

Welcome to MrExcel.

Perhaps this will help?

Either use a 'helper' columnl (New column c) and make appropriate references to C in your formulas, rather than the current B references.

OR incorporate the formula C3 directly, in your other formulas. Eg as in E4.
Excel 2007

Excel 2007

	B	C	D	E
2	Purchase Date		useful life in yrs	est end date
3	15/05/2012	15/05/2012	4	14/05/2016
4	15/05/2012		4	14/05/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

	B	C	D	E
2	Purchase Date		useful life in yrs	est end date
3	21/05/2012	21/06/2012	4	20/06/2016
4	21/05/2012		4	20/06/2016

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C3 =IF(DAY(B3)<16,B3,DATEVALUE(DAY(B3)&"-"&MONTH(B3)+1&"-"&YEAR(B3))) E3 =IF(D3<=0," ",C3+(D3*365)) E4 =IF(D4<=0," ",IF(DAY(B4)<16,B4,DATEVALUE(DAY(B4)&"-"&MONTH(B4)+1&"-"&YEAR(B4)))+(D4*365)) <tbody> </tbody>

<tbody>
</tbody>

Hope that helps.

I will definitely try it Thank you! I will let you know.