Various date formats in same column

**mardiefe** · 06-22-2014, 08:57 AM

Hello

I need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect

Any help greatly appreciated. Thanks!
mardiefe

**popipipo** · 06-22-2014, 09:27 AM

Please post a file with different formats

**sktneer** · 06-22-2014, 09:30 AM

One way to achieve this is that you can place three button on the sheet, those when clicked will change the date format of the desired date column.
Please find the attached sheet to see if this is something you can work with.

**newdoverman** · 06-22-2014, 09:34 AM

You can determine part of what the problem is by selecting the column of "dates" and format the column as GENERAL. All of the real dates will be turned into their serial numbers. The others will remain as text.

If you can upload your workbook with the column of dates, perhaps we could determine a method of producing real dates from what you have. It is also possible that in order to get dates, you are either going to have to find the dates from other sources or come to a policy of using say the 1st of the month instead of partial dates. Where you only have years, the 1st of the year may be the only thing you can use. If you are not the one with the authority to determine this, find out what policy should be followed when confronted with partial dates. Let us know what this policy is and then maybe we can help further.

**popipipo** · 06-22-2014, 09:35 AM

communication failure

**sktneer** · 06-22-2014, 09:42 AM

Moreover if you don't want to change all dates to a single format and want to change different dates to the different format, you can also do so by selecting the desired date cells and clicking on the desired date format button on the sheet.

**mardiefe** · 06-22-2014, 03:32 PM

Hi everyone

Thanks for your replies. I try to be more specific and added an example file. I cannot add the original as it contains sensitive data

So the data I might be entering could be as in row 2 (just a year, i.e. "2015"), as in row three (full date), or as in row four (just the month and the year). The reason for that is, that sometimes date and / or month are not yet available. I would like that the cell automatically shows up a proper form such as:

row 2: 2015
row 3: 09.04.2014
row 4: 04.2014
row 5: 2016
row 6: 03.12.2016
row 7: 04.2015

I read in another forum (http://www.mrexcel.com/forum/excel-q...-optional.html) that I can tackle this with conditional formatting, however there the issue was with days and hours. So my question would be, how can I set the format of cells that it shows entries like in my shown list above, no matter whether I only enter a year, a year and a month or the full date. Is that achievable without macros? (I'm aware of that I could use some macro, but I am not allowed to run those in my office).

Thanks for your support (if this is at all solvable without macros).

mardiefe