Dear Friends,

I have two dates (UN-SEP-2006 and 28-Jun-17) where the first date day value is UN (Unknown). In this case, How do I find the difference between these two dates? Please suggest.

Other Excel Questions

Excel Tutorials

0

Dear Friends,

I have two dates (UN-SEP-2006 and 28-Jun-17) where the first date day value is UN (Unknown). In this case, How do I find the difference between these two dates? Please suggest.

0

Selected Answer

You have to first supply a value for **UN**.

Otherwise, you will never know the difference because you don't know the day for the first value.

I would suggest using a text editing function to replace UN with a placeholder value, such as 1 and just default the dates with UN to the first of the month.

With the UN date in cell A1, use a function like this:

`="1-"&RIGHT(A1,LEN(A1)-FIND("-",A1))`

Then select that cell and hit: Ctrl+C, Alt+E+S+V Enter. Then you can convert the cell into a proper Excel format one of many ways, the easiest of which is to double-click the cell and hit Enter.

Now, use regular date functions for the two dates.

Hi Don,

Great, Its perfectly working. Thanks for your kind effort to update me.

Am not good at excel functions. Could you please explain bit on the text editing function you have uesd here?!!.

Regards,

hendryth

Great, Its perfectly working. Thanks for your kind effort to update me.

Am not good at excel functions. Could you please explain bit on the text editing function you have uesd here?!!.

Regards,

hendryth

hendryth (rep: 2) Oct 26, '17 at 11:44 am

This** RIGHT(A1,LEN(A1)-FIND("-",A1))** locates the first dash in the cell and then returns everything after that dash. Lookup tutorials on teachexcel for the 3 functions in this formula and you will get a detailed explanation of how they work. That's the best way to understand it as these functions can seem confusing at first.

don (rep: 1287) Oct 29, '17 at 2:13 pm