Adjusting data in cell

[AlexanderBB]

I formatted a column to custom 'dd mmm yyyy' which is mostly what's wanted but some of the data has year only.
So I've been entering 'dd mmm 1999'. Now I don't like it as there's too many, and having the year only is tidier.
Changing the Format to text allows the year only but changes the full formatted Date to a number.
Some values also don't have a year so "07 Apr 19?" is used.
Is there a format where I can have all those without (as someone said) having a leading apostrophe?
And I think I need to remove the value for each cell, reformat it, and reapply the value.

 

[Joe4]

There isn't really a good way to have mixed data types in a single column under the same format.
Your best bet would probably be to format everything as Text, and then convert what you need, if used in other formulas.

Note key to understanding this is to understand how dates are exactly stored in Excel. Dates are really just stored as numbers, specifically the number of days since 1/0/1900.
You can easily see this by entering any valid date, then changing the format of that cell to Number or General. Then you will see the date as Excel does.
So all dates really are in Excel are numbers with special date formatting applied.

That means if you have a column formatted as any date format, and enter a number in it like 1999, it will display it in a date format, specifically 1999 days from 1/0/1900, which is just 6/21/1905 (or 21/6/1905 if you are using a European date format).

If you want to enter dates, but only enter the month & day and have the year default to 1999, we can come up with some VBA code in order to do that.
But the only way that I know of to do that involves VBA.

 

[AlexanderBB]

A "just put what I type in" category would be really useful. So you're not fighting what it wants to do.
At the moment it's Custom format "dd mmm yyyy" and shows as 16 May 1929. I want "16 May 1929"
What I've had to do is

d = Cells(i, "G")
g = Day(d) & Space(1) & MonthName(Split(d, "/")(1)) & Space(1) & Year(d)
Cells(i, "G") = ""
Range("G" & i).NumberFormat = "@"
Cells(i, "G") = g

But each variation of Cells(i"G") needs sorting out first.
There's no better way to change the cell format and display what is there (as it's shown)?

 

[Scott Huish]

I can get you the first two with a number format, but how are you entering the ones without a year?

 

[AlexanderBB]

12 Jun 18?, 13 may 19?
I found MonthName needs to be 3 letter version

Function Shortmonthname(t)
    Shortmonthname = Format("01 " & MonthName(t) & " 2012", "mmm")
End Function

 

[Scott Huish]

That function doesn't change 12 Jun 18? into Jun

 

[Scott Huish]

If you're just trying to get out the middle section, Split seems like a better fit:

Function Shortmonthname(t) As String
    Shortmonthname = Split(t)(1)
End Function

 

[AlexanderBB]

Yes, I'd split before calling the function.
Some d & m are "dd mmm 1944" e.g. when only the year is known.
That's the only other variation

 

[Scott Huish]

OK. I think I understand now. You have data that looks like in Column A. Are the results that are in Column B what you want it changed to?

Book1
	A	B
1	Date	Wanted Results
2	7 Apr ??	Apr
3	dd mm 1995	1995
4	4/7/2023	07 Apr 2023
Sheet1

 

[Alex Blakenburg]

I like Joe would generally avoid mixed data types and would also try to avoid changing dates into Text which makes it difficult to do calculations etc with it.
It seems like you are happy to go the text route.

To do this:
1) Convert existing dates to Text
• Highlight the column
• Text To Columns > Delimited (Tab is fine) > In Step 3 to select Data Type select "Text" > Finish
2) Change the column Number format to Text

Step 1 overcomes the issue you are having with changes the full formatted Date to a number
Step 2 will keep new entries exactly as entered since it will be assume the data entered to be text (without having to enter a leading apostrophe)