Formula reads only till 12th on month

Solved/Closed
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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



2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
0
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,
0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
That would be one way. But if you dont want to change the system date and just want to convert data date to your system date format, you could use what I mentioned. Well as long as you got the issue resolved, nothing else matters Good Luck
0