Formula reads only till 12th on month Solved/Closed

Question

Hello, 
If I run the following formulae it gives the result only from 1st to 12 of the month. Is there any thing wrong with the formula or with my Excel ?

=IF(ISERROR(DATEVALUE(MONTH(H9)&"/"&DAY(H9)&"/"&YEAR(H9))),"",DATEVALUE(MONTH(H9)&"/"&DAY(H9)&"/"&YEAR(H9)))

				29-04-2010
				30-04-2010
05-01-2010				01-05-2010
05-02-2010				02-05-2010
05-03-2010				03-05-2010
05-04-2010				04-05-2010
05-05-2010				05-05-2010
05-06-2010				06-05-2010
05-07-2010				07-05-2010
05-08-2010				08-05-2010
05-09-2010				09-05-2010
05-10-2010				10-05-2010
05-11-2010				11-05-2010
05-12-2010				12-05-2010
				13-05-2010
				14-05-2010
				15-05-2010
				16-05-2010
				17-05-2010
				18-05-2010
				19-05-2010
				20-05-2010
				21-05-2010



Configuration: Windows 7 / Internet Explorer 7.0

rizvisa1 · Answer

What are you trying to do there ? If you just wanted to convert this to date. The most easy way is

1. Select the column in which these values are
2. Go to DATA and select Text To Column
3. Choose Delimited and select next
4. Clear all the checks and click next
5. Click on "Data" option and choose DMY
6. Click on finish

sapvyk · Answer

Hello rizvisa1,

Thanks for replying.
Actually I got the Answer, Excel was actually picking the systems date format. When I changed the date format to MMddyyyy in system it worked. Thats the reason it was only calculating till 12 for 12 months....

Regards,

Formula reads only till 12th on month

2 responses

Similar discussions

Subscribe To Our Newsletter!