Hello, I have a report where the date is reported as dmmyyyy, so example 8052021 - the problem I have is that I cannot convert this to show 5/8/2021 due to the 1 digit day. If I have 16052021 I can easily convert it to 5/16/2021 but it does not work for the one digit day ones. Does anyone have a fix for this?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Number date to actual date conversion issue
- Thread starter SusieL
- Start date
FormR
MrExcel MVP
- Joined
- Aug 18, 2011
- Messages
- 7,148
- Office Version
- 365
- Platform
- Windows
Hi, welcome to the forum. Assuming your regional settings are set for UK/EU date formats, you could try this.
You may need to format the cell that contains the formula as a date.
| Book1 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | 8052021 | 08/05/2021 | ||
Sheet1 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | B1 | =0+TEXT(TEXT(A1,REPT(0,8)),"00\/00\/0000") |
You may need to format the cell that contains the formula as a date.
Upvote
0
FormR
MrExcel MVP
- Joined
- Aug 18, 2011
- Messages
- 7,148
- Office Version
- 365
- Platform
- Windows
If your regional setting are for US date formats then you can use this formula to replace the leading zero from which you can probably continue with the method you are currently using for the two digit days.
Excel Formula:
=TEXT(A1,REPT(0,8))
Upvote
0
Similar threads
- Question
