Entering numbers in Excel to get Dates

Question

In Excel I need to be able to enter 04012026, and get 04/01/2026. How can this be done?

Answer 1

Hi @Darrell Speegle,

It is not very simple to convert numbers given as texts to dates, since it depends on how these numbers are given.

However, below I am giving you a solution through PYTHON formulas.

In column A, numbers are given as texts that must be converted to dates. These numbers are conceived with 8 numbers that represent a full date, i.e. mm/dd/yyyy or with 7 and 6 numbers. The case with 6 numbers represents the short date, i.e. mm/dd/yy. The formulas work for: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web.

(see this link: https://support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d)

In cell D2, this PYTHON formula has been applied:

pd.to_datetime(xl("A2"))

In cell D3 and below, this formula has been applied:

pd.to_datetime(xl("A3"), format = '%m%d%Y')

After you have applied the formula in D3, drag it down.

How are formulas applied?

In cell D2, type =PY and then use the Tab key on your keyboard. In the cell, you will see a green symbol with the letters PY inside it. Then in this cell enter the PYTHON code, for example pd.to_datetime(xl("A2")) and immediately while holding down the Ctrl key on the keyboard press the Enter key and the formula will be applied, giving the expected result. The results obtained from the PYTHON formula can then be converted to Excel values.

HTH

IlirU

Answer 2

I understand my answer does not help in your case. Try this other option to convert the date format from mmddyyyy to the Excel date format:
Make sure the date cell is in Text number format, otherwise, it will delete the "0" on the left of the value.

First, you need to separate the month, day, and year in different cells.
Using the formula =MID, you have to enter the cell it will get the data from, the starting character number and the quantity of characters.

B1 cell has the month information, it is taking the data from the A1 cell (date), from the first character (1), two characters (2)
=MID(A1,1,2)

C1 cell has the day information, it is taking the data from the A1 cell (date), from the third character (3), two characters (2)

=MID(A1,3,2)

D1 cell has the year information, it is taking the data from the A1 cell (date), from the fifth character (5), four characters (4)

=MID(A1,5,4)

The E1 cell will output the date using the =DATE formula. Insert the cells it will get the information from in the following order: year, month, day.
D1= year

B1= month

C1= day

=DATE(D1,B1,C1)

Answer 3

Hi, I'm Rodrigo and I will help you.
In Excel, select the cells that you want work this way, select the drop-down menu on the image below, and select "Short date". This will be applied to all selected cells.