# calculate datedif on partial dates

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.

### Discussion

Can I use Datedif function in this case?
hendryth (rep: 2) Oct 26, '17 at 3:40 am
Can anyone suggest?
hendryth (rep: 2) Oct 26, '17 at 3:43 am
I answered it below. Comment on that answer if it is not clear what to do.
don (rep: 1969) Oct 26, '17 at 10:53 am

0

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.

### Discussion

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
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: 1969) Oct 29, '17 at 2:13 pm