Formatting 2-digit year in MS Access. How?

[Luuk_Hessing]

I need a 2-digit year notation and a 2-digit month notation from a date field.

Using the helppage of Ms Access the 2-digit month notation wasn't much of a problem after "mm" behind notation and using the magic button.

The 2-digit year notation is my problem. The instructions on the helppage didn't give the required result. Each and every attemp resulted in a 4-digit year notation.

In the setup of Windows10 I have changed the short time notation from "dd-mm-yyyy" into "dd-mm-yy" (this is the European sequence). After that I restarted my PC. The PC gives "dd-mm-yy" for short time notation.

However in MS Access the short notation remains "dd-mm-yyyy". 

What an I doing wrong? How can I get a 2-digit year notation?

I'm using Microsoft365.

Regards.

[#2]

@Luuk_Hessing 

Keeping in mind that what you are talking about is a FORMAT FOR DISPLAY PURPOSES, and not the actual DATE VALUE STORED, you can do this with a Format() expression wherever you need to DISPLAY that FORMATTED STRING.

Format(Month(Date), "mm") & "/" & Format(Year(Date),"yy")

or

Format(Month(Date), "mm") & "-" & Format(Year(Date),"yy")

Having done this, you are DISPLAYING a string, not a date, so beware of trying to use it for any further date logic.

[#3]

@George_Hepworth 

This is such a common request that I feel it's useful to jabber on about it a bit more.

Access ALWAYS stores dates internally as a full date and time. It does so in the form of a double.

The part of the double to the left of the decimal is the number of elapsed days since the starting date for Access dates, which is December 30, 1899 (or 12/30/1899 in the US format used by Access be default)

The part of the double to the right of the decimal is the elapsed time since midnight of the date specified. For example, 8:00AM is 8 hours after midnight, or 8/24th of a day, or 1/3 of a day, or .3333 

So the current date and time might be 44334.3333 representing 8:00 o'clock in the morning on the 18th of May, 2021. 

Note that in my example, I actually used several different FORMATS to express dates and times. We, humans, have learned to interpret a number of DATE FORMATS so we just do it without thinking.

The important point is that no matter how the date and time are expressed, the underlying value is what Access actually stores in the table. And it stores that value as I explain above.

So, when working with dates and times in a relational database application, be careful not to confuse the ability to FORMAT a date for DISPLAY with the actual value it represents.

[#4]

@George_Hepworth 

I was not aware how Access is storing its date/time. Thank you for this explication.

Please, also read my other reply.

[#5]

Thank you for the above information.
I still wander why it is possible to get the 2-digit month number but not the 2-digit year number.
As I wrote before the 2-digit month number wasn't a problem within Access; it's the 2-digit year that keeps on showing up as a 4-digit number. Why?

[#6]

I should have been more clear. The year is a 4-digit value. To DISPLAY it as a 2 digit number, you apply the appropriate format to it.

[#7]

Go to menu Files, then Settings, select Client Settings, scroll down to section General, and uncheck marks for "Use four-digit formatting of years".

[#8]

Hello George,
And that is the problem. I've checked the Access helppage for the format and the helppage suggested it was possible to get a 2-digit year number. Their instructions worked for the 2-digit month number, but not for the 2-digit year number.

[#9 · Accepted solution]

Hello Gustav,
And that was the way to do it. The option you referred to, I didn't realize that it was existing. Thank you very much.
Regards from the Netherlands.

[#10]

Great! Then please mark as "best response" to serve future readers.

[#11]

My apologies. I did not realize you wanted to make this a universal setting throughout all of your accdbs.

[#12]

@George_Hepworth You have two options:

• This application
• All applications

Pick your choice (or none).

[#13]

Hmm. That option was grayed out the first time I looked....

[#14]

As it is a client setting, that presumably means individual users can untick it again
Perhaps it would be better to set this using code in order to ensure it applies?

[#15]

@Gustav_Brock I don't know if this will be seen or not. I did a Google search for this very subject and came across this thread. The problem that I'm facing is that the boxes for four-digit year formatting are ALREADY UNCHECKED. However, my database continues to apply four-digit years to some of my fields. Not all of them, but only some of them. I would very much prefer a two-digit year, but ultimately I want consistency and for some reason it is giving me four digits for birth years in the 1930's and 1940's and two digits for all birth years since 1950. Any ideas why?

[#16]

@Meredith Marr 

Access assumes that two-digit years before 50 belong to 2000 while those 50 and after belong to 1900:

? DateValue("49-01-01")
2049-01-01

? DateValue("50-01-01")
1950-01-01