Date Format Issue

[shapenote]

I am trying to change the date format in a spreadsheet and nothing seems to work.

The dates are coming in as 'dd.mm.yyyy ... I have tried removing the ', but when I delete it, excel puts it back. I tried doing a alt edit replace, but excel says it can't find the ' in the cells.

I have tried using =Value and =DATEVALUE to see if I can get a number that way and then format that to a date, but that doesn't work either.

So I am not sure what is up with the formatting of these dates and why it won't let me change them.

I need to change them to just be dd-MON-yy so I can filter on them. As they are now, the filter won't clump them together by Months.

Thoughts, suggestions?

 

[shapenote]

Ok, so that formula worked to convert it to a DD-MM-YY format, but the wrong date. :-|

For example, my A1 has the text '01.03.2018. When I put in the =Date formula, my return is 1-Dec-17.

So i get the format I want, just the wrong date.

 

[JoeMo]

If post #2 is referring to a post I deleted then try this instead:

Excel Workbook
	A	B	C
1	26.01.2019	26-Jan-2019	TRUE
Sheet1

 

[shapenote]

Ah! Yes I was referring to you previous post.

I tried this formula and it worked! Thanks!

one follow up question, not sure I follow the logic of the formula though. Why using Mid and what's the difference between 7,4 or 4,2, etc?

 

[Dave Patton]

another alternative to try

Excel 2010
	A	B
1	01.03.2018	1-Mar-18
2	26.01.2019	26-Jan-19
2a

Cell Formulas
Range	Formula
B1	=--SUBSTITUTE(A1,".","-")

 

[MARK858]

As another alternative just doing a simple Find/Replace of . with / then formatting the cells as dd-mm-yyyy works for me in the UK.

 

[JoeMo]

Ah! Yes I was referring to you previous post.

I tried this formula and it worked! Thanks!

one follow up question, not sure I follow the logic of the formula though. Why using Mid and what's the difference between 7,4 or 4,2, etc?

There are other ways to do this. I just happened to choose the MID function which uses the following syntax:

MID(text, start_num, num_chars)

where text is the text you want to convert to a date, start_num is the character position of the first character you want MID to return, and num_chars is the number of characters, including the start character you want MID to return. Note that your text format ('dd.mm.yyyy) has 11 characters including the leading apostrophe, but the apostrophe is ignored by Excel b/c by default it is used to convert whatever follows it to text.