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

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

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

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]2[/TD]
[TD="bgcolor: #ffffff"]Purchase Date[/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff"]useful life in yrs[/TD]
[TD="bgcolor: #ffffff"]est end date[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]21/05/2012[/TD]
[TD="bgcolor: #ffff00, align: right"]21/06/2012[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20/06/2016[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]21/05/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20/06/2016[/TD]

</TBODY>

Sheet2​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]C3[/TH]
[TD="align: left"]=IF(DAY(B3)<16,B3,DATEVALUE(DAY(B3)&"-"&MONTH(B3)+1&"-"&YEAR(B3)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]E3[/TH]
[TD="align: left"]=IF(D3<=0," ",C3+(D3*365))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]E4[/TH]
[TD="align: left"]=IF(D4<=0," ",IF(DAY(B4)<16,B4,DATEVALUE(DAY(B4)&"-"&MONTH(B4)+1&"-"&YEAR(B4)))+(D4*365))[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]



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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFFFF"]Purchase Date[/TD]
[TD="bgcolor: #FFFFFF, align: right"][/TD]
[TD="bgcolor: #FFFFFF"]useful life in yrs[/TD]
[TD="bgcolor: #FFFFFF"]est end date[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15/05/2012[/TD]
[TD="bgcolor: #FFFF00, align: right"]15/05/2012[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14/05/2016[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]15/05/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14/05/2016[/TD]

</tbody>

Sheet2​

	B	C	D	E

<tbody>
[TD="align: center"]2[/TD]
[TD="bgcolor: #ffffff"]Purchase Date[/TD]
[TD="bgcolor: #ffffff, align: right"][/TD]
[TD="bgcolor: #ffffff"]useful life in yrs[/TD]
[TD="bgcolor: #ffffff"]est end date[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]21/05/2012[/TD]
[TD="bgcolor: #ffff00, align: right"]21/06/2012[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20/06/2016[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]21/05/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20/06/2016[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]C3[/TH]
[TD="align: left"]=IF(DAY(B3)<16,B3,DATEVALUE(DAY(B3)&"-"&MONTH(B3)+1&"-"&YEAR(B3)))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]E3[/TH]
[TD="align: left"]=IF(D3<=0," ",C3+(D3*365))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]E4[/TH]
[TD="align: left"]=IF(D4<=0," ",IF(DAY(B4)<16,B4,DATEVALUE(DAY(B4)&"-"&MONTH(B4)+1&"-"&YEAR(B4)))+(D4*365))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Hope that helps.

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