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

lavenlake

New Member
Joined
Mar 10, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

littlepete

Active Member
Joined
Mar 26, 2015
Messages
251
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

New Member
Joined
Mar 10, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top