how to check date difference in excel

[springran]

Hi there,

In A1 I have 01.05.2017 in B1 I have a formula which show a date ( this date come from other filed). what formula should I use to calculate difference between A1 and B2?

thanks

 

[footoo]

Calculate the difference between two dates - Excel

 

[springran]

sorry that's not working
In B1 I have a formula (IF(IFERROR(SEARCH("manually",S2,1),0)=1, MID(S2,FIND(" ",S2,1),9), "")) which shows a date in B1.

 

[footoo]

Try this :

=(IF(IFERROR(SEARCH("manually",S2,1),0)=1, DATEVALUE(MID(S2,FIND(" ",S2,1),9)), ""))

and formate B1 as Date

 

[springran]

I guess the formula is for B1 and I need to change formate as date. I have tried to use this formula in B1. it's not working.
let me clarify bit more
I manually type different sentences in S2. so in B2 It has a formula IF(IFERROR(SEARCH("manually",S2,1),0)=1, MID(S2,FIND(" ",S2,1),9), "") which take date from S2. In my A2 I have date as well ( no formula).
Now I want a formula to check how many days between A2 and B2.

 

[Jonmo1]

Can you post a few examples of the text written in S2 ?

 

[springran]

in S2

Manually 26.07.17

<tbody>
</tbody><colgroup><col></colgroup>

ASN 07.07.17. location. 2 way

Manually 28.06.17.PO line

<tbody>
</tbody><colgroup><col></colgroup>

 

[SyedUsman]

Try This
=DATE("20"&RIGHT(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),2),MID(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),4,2),LEFT(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),2))

Then Take Difference

 

[springran]

Try This
=DATE("20"&RIGHT(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),2),MID(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),4,2),LEFT(RIGHT(S2,LEN(S2)-SEARCH(" ",S2,1)),2))

Then Take Difference

HI SyedUsman

this only works if the date is last entry. for example ASN 01.02.17 or manually 15.02.17
if I have something after the date. the formulas is not working. for example ASN 19.08.17. Location.PO (this in one filed). it will return as #value .

 

[SyedUsman]

=date("20"&right(right(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),len(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)))-search(" ",if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),1)),2),mid(right(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),len(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)))-search(" ",if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),1)),4,2),left(right(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),len(if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)))-search(" ",if(len(mid(s2,max(1,find(".",s2)-3),11))=9,(mid(s2,max(1,find(".",s2)-3),11)),substitute((mid(s2,max(1,find(".",s2)-3),11)),".","",3)),1)),2))