calculate datedif on partial dates


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.



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
Add to Discussion


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:


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?!!.
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
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login