I need to be able have dates as either yyyy or dd mmm yy date format in the same column, is this possible?

[lavenlake]

I have a sheet where I am tracking dates pertaining to certifications. The issue is that some certifications have only a year (yyyy) (2013, 2007, 2020, etc.) or they have an exact date that I need to put in a dd mmm yy format (06 Jun 19, 15 Jan 07, 33 Oct 20). Some people also don't have the exact dates from their certification, in which case I can only use the year. These two date formats need to be interchangeable in the same cell.
When I use ctrl+1 and use the custom tab to format the dates to one of these options I get issues when I need to enter the other date format. It's not only the wrong format but it will also change the date to something completely random (I'll type in 01 Jul 20 and it'll change to 1905 or I type in 2022 and it changes to 15 Jun 05). I'm not very excel savvy but it's something I've been tasked with at work. Help?

 

[jasonb75]

Welcome to Mr Excel

The problem that you're encountering is in the way that Excel handles dates, which is by counting the number of days from 01 Jan 1900 (in your example, 15 Jun 05 will actually be 15 Jun 1905, 2022 days from 1 Jan 1900).

The easiest way around this is with a custom format (see note below before trying this), unless you plan on entering dates older than 18 March 1908, this will be good for dates until 31 Dec 2999.

[<3000]General;dd mmm yy

That said, my personal preference would be to enter dates where you only have a year as the end of the year. For example, enter 2022 as 31 Dec 2022.
The reason for this is that the year only dates would not be comparable to full dates for reporting purposes, charts, etc.

 

[littlepete]

I have a sheet where I am tracking dates pertaining to certifications. The issue is that some certifications have only a year (yyyy) (2013, 2007, 2020, etc.) or they have an exact date that I need to put in a dd mmm yy format (06 Jun 19, 15 Jan 07, 33 Oct 20). Some people also don't have the exact dates from their certification, in which case I can only use the year. These two date formats need to be interchangeable in the same cell.
When I use ctrl+1 and use the custom tab to format the dates to one of these options I get issues when I need to enter the other date format. It's not only the wrong format but it will also change the date to something completely random (I'll type in 01 Jul 20 and it'll change to 1905 or I type in 2022 and it changes to 15 Jun 05). I'm not very excel savvy but it's something I've been tasked with at work. Help?

hello,
i'm not a good excel user but this is what you could do:
add an extra column, in which you put 1 (15.02.2020) or 2 (15.02.20) or 3 (20.02.15) and so on...
then you can put conditional formatting on the column with the dates, depending on the value in the next column...
i'm not good at the conditional formatting but i know it works, maybe someone else can give you the correct way
good luck !

 

[lavenlake]

Welcome to Mr Excel

The problem that you're encountering is in the way that Excel handles dates, which is by counting the number of days from 01 Jan 1900 (in your example, 15 Jun 05 will actually be 15 Jun 1905, 2022 days from 1 Jan 1900).

The easiest way around this is with a custom format (see note below before trying this), unless you plan on entering dates older than 18 March 1908, this will be good for dates until 31 Dec 2999.

[<3000]General;dd mmm yy

That said, my personal preference would be to enter dates where you only have a year as the end of the year. For example, enter 2022 as 31 Dec 2022.
The reason for this is that the year only dates would not be comparable to full dates for reporting purposes, charts, etc.

Thank you, this seemed to work!