Help me in date format

[Rahul Mishra]

i have two format of date 17.02.2014 and 17-Feb-2014 but don't change date format but required after 4 month date from both format 17-May-2014

 

[Khalid NGO]

Hi Rahul,
is this different type of question you shared here? :
http://chandoo.org/forum/threads/help-me-in-date.20955/#post-126384

 

[Somendra Misra]

Hi Rahul,

These two formats are in different cells, say A1 & B1?

So after 4 months both cells should have the date as 17-Feb-2014?

Is this you want or illustrate on a file.

Regards,

 

[Rahul Mishra]

sir it is new problem plz help me

 

[Rahul Mishra]

Warranty S
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17.02.2014
17-Feb-14
17-Feb-14
17-Feb-14
17-Feb-14
this type of data i have in excel and i want after 3 month date 17-may-2014

 

[Rahul Mishra]

but one problem in it can not change format

 

[Khalid NGO]

Hi Rahul,
Please share your question in a sample file with expected result for both dates.

 

[Rahul Mishra]

file attached please help me in this

 

[Khalid NGO]

Thanks,
Try this:

=EDATE(SUBSTITUTE(C4,".","/")+0,3)

 

[Rahul Mishra]

not working sir

 

[Khalid NGO]

Its working Rahul,

See the attached.

 

[Rahul Mishra]

17.02.2014
in this format not working that is my problem

 

[Khalid NGO]

What version of excel you are using?

 

[Rahul Mishra]

10

 

[Khalid NGO]

I am using 2007, and its working fine:



I've found the issue with 2003 but not sure with 2010:
http://answers.microsoft.com/en-us/...07-edate/953975b4-9d19-4948-80cb-ecc6f4996ac8

Regards,

 

[udprocks]

I am using 2007, and its working fine:

View attachment 14221

I've found the issue with 2003 but not sure with 2010:
http://answers.microsoft.com/en-us/...07-edate/953975b4-9d19-4948-80cb-ecc6f4996ac8

Regards,

Yes khalid sir,

Its not working in 2010 i have tried this and getting error.

 

[NARAYANK991]

Hi Rahul ,

Can you confirm what is your system date format ?

I have opened Khalid's file in Excel 2010 , and everything is OK.

Narayan

 

[Khalid NGO]

Yes khalid sir,

Its not working in 2010 i have tried this and getting error.

Hi Jitendra,
Same question for you, as asked by Sir Narayan.

 

[Khalid NGO]

Just an alternate solution:

=IF(ISNUMBER(FIND(".",C4)),SUBSTITUTE(C4,".02.",".05."),DATE(YEAR(C4),MONTH(C4)+3,DAY(C4)))

But you will not get actual dates if the condition is TRUE.

 

[udprocks]

Hi Jitendra,
Same question for you, as asked by Sir Narayan.

Hallo sir,

My date format is ..

mm/dd/yyyy

 

[NARAYANK991]

Hi Jitendra ,

That is the reason the formula will not work ; even after replacing the period "." with the slash "/" , 17/02/2014 is not a valid date , since the format is mm/dd/yyyy.

One way is to use the following formula :

=IF(TYPE(C10)=1,EDATE(C10,3),EDATE(DATE(RIGHT(C10,4),MID(C10,4,2),LEFT(C10,2)),3))

where C10 is a cell which has the date in the format 17.02.2014 or 17-May-2014.

Narayan