Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

Answer
Discuss

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: 1989) Oct 26, '17 at 10:53 am
Add to Discussion

Answers

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.

Discuss

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: 1989) 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