Dates

[Shay A]

Hello,
In the file attached, I have, in column A, dates entered manually. My problem is that Excel doesn't consider many of the cells as dates. (I used the ISNUMBER function to examine this). How can I fix this? I want ALL the cells in column A to be considered as numbers.
My desirable fprmatting for date is dd/mm/yyyy.

Thanks!

 

[Khalid NGO]

Hi,

Use a helper column, for example column C:

C2:

=IF(ISNUMBER(A2),A2,DATE(2000+RIGHT(A2,2),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),MID(A2,FIND("/",A2)-2,2)))

copy down...
Once you get the result, replace it with column A.

Regards,

 

[Asheesh]

Hi,
Can you explain the below two date formats

2/7/2015

Is it 2nd of July or 7th of Feb

20/7/2015

And this for sure is 20th of July


If the first one is 7 the of Feb then the file has inconsistent date formats..what is the logic to identify the dates in that case.

Confirm?

 

[NARAYANK991]

Hi ,

A similar question has been asked and answered here :

http://chandoo.org/forum/threads/hi...rom-9-9-2015-to-09-09-2015.26989/#post-161350

Narayan

 

[Khalid NGO]

Hi ,

A similar question has been asked and answered here :

http://chandoo.org/forum/threads/hi...rom-9-9-2015-to-09-09-2015.26989/#post-161350

Narayan

Text to Column is amazing.
Thank you Sir.

 

[Shay A]

thanks for all of the responses!
What about this coulmn, how would the formula change? @Khalid NGO
If the user attempted to type a date, I need the Excel to consider it as a date. however, if it is a string of letters or a combination of letters and numbers, the formula should keep the original value.

TY

 

[Khalid NGO]

thanks for all of the responses!
What about this coulmn, how would the formula change? @Khalid NGO
If the user attempted to type a date, I need the Excel to consider it as a date. however, if it is a string of letters or a combination of letters and numbers, the formula should keep the original value.

TY

In that case, you need to wrap the above formula with IFERROR:

=IFERROR(IF(ISNUMBER(A2),A2,DATE(2000+RIGHT(A2,2),SUBSTITUTE(MID(A2,FIND("/",A2)+1,2),"/",""),MID(A2,FIND("/",A2)-2,2))),A2)

Red part update

Regards,

 

[Shay A]

and what about the reverse slash (\)?
lets say 20\7\2015

 

[vletm]

@Shay A -- Do You know, how many dates are there?
Have You tried this 'manual' way:
1) select A-column
2) Right Click -> Format Cells -> Custom -> Type: dd/mm/yyyy -> OK
3) add formula wanted cell ( not A-column )
=countif(A:A;">=1/1/1900")

 

[Shay A]

yes I have tried manually but id didnt change Excel not considering it as a number

 

[vletm]

@Shay A
Why do You need number?
You're counting dates ...
Did You test that formula?

 

[Shay A]

@vletm
I am building a dashboard so I want the dates to have a unified format as well to be organised chrologically on the X axis. both things don't work at the moment.

 

[vletm]

@Shay A
hmmm .. which number will 'patch not installed'?
I tried to sort too ... what kind of challenge do You have?
What's wrong after Sorting?
Of course, few wrong way written 'dates' will be challenge and non 'dates' goes to the end of list.

 

[Khalid NGO]

and what about the reverse slash (\)?
lets say 20\7\2015

Use substitute to replace reverse slash with slash like this:

=IFERROR(IF(ISNUMBER(A2),A2,DATE(2000+RIGHT(A2,2),SUBSTITUTE(MID(SUBSTITUTE(A2,"\","/"),FIND("/",SUBSTITUTE(A2,"\","/"))+1,2),"/",""),MID(SUBSTITUTE(A2,"\","/"),FIND("/",SUBSTITUTE(A2,"\","/"))-2,2))),A2)

You will be ended up with formula solution if you have more doubts.

Advice: Whenever you (or anyone) want to enter a date, enter it properly.

Regards,