calculate with date format

[xymcrush17]

hi mr excel i have question
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD]2013-12-20[/TD]
[TD]18
[/TD]
[TD]2014-01-10[/TD]
[TD]0
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD]2014-01-04[/TD]
[TD]0
[/TD]
[TD]2014-02-10[/TD]
[TD]0
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD]2014-02-01[/TD]
[TD]-4
[/TD]
[TD]2014-03-01[/TD]
[TD]0
[/TD]
[TD]-25
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD]2014-01-29[/TD]
[TD]28
[/TD]
[TD]2014-03-10[/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

Red words is the result that my expected

where

D1 = C1-B1 (date)

F1 = E1-C1 (month) (and different month will subtract 1)

G1 = E1-B1 (date)

thank you in advance
warm regard

 

[xymcrush17]

help pls anyone

 

[pjmorris]

hi,

in D1: =DAY(C1)-B1
in F1: =DATEDIF(C1,E1,"m")
in G1: =DAY(E1)-B1

note that datedif is an undocumented function, see this for more detail: Excel DATEDIF Function worth noting that as shown here datedif returns complete months so is not quite the same outputs as your proposed answers.

Hope this helps.

Regards

 

[xymcrush17]

hi,

in D1: =DAY(C1)-B1
in F1: =DATEDIF(C1,E1,"m")
in G1: =DAY(E1)-B1

note that datedif is an undocumented function, see this for more detail: Excel DATEDIF Function worth noting that as shown here datedif returns complete months so is not quite the same outputs as your proposed answers.

Hope this helps.

Regards

Thank you

F1= DATEIF is return !num#

 

[pjmorris]

I assume you actually entered DATEDIF rather than DATEIF, if so I you'll need to check the format of the input cells. It worked for me and I simply cut and pasted from your original post.

HTH

 

[xymcrush17]

I assume you actually entered DATEDIF rather than DATEIF, if so I you'll need to check the format of the input cells. It worked for me and I simply cut and pasted from your original post.

HTH

http://simaweb.fe.undip.ac.id/foto/sample.xlsx

here is my data.. i think the yellow row is should be 1, isnt?

 

[pjmorris]

hi, the datedif as setup here returns 1 because there is 1 full month between the two dates, hence the formula correctly returns a blank. If you want to compare the actual month numbers you need to use month(date)-month(date) but this fails when the dates are in different years. Not sure this is very helpful - it depends on what precisely you want to achieve.

Regards

 

[xymcrush17]

hello peter
thank you for reply

i have case for this math in excel

http://www.mrexcel.com/forum/excel-questions/875561-calculate-fine-date-format.html