I have inherited a spreadsheet which has a mix of formats where it should have dates only. on top of this, the way the cell contents are arranged, the contents resemble US dates (mm/dd/yyyy) wher i need dd/mm/yyyy. what is the best way to reformat all to the one UK format?
-
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.
date time mixed formats
- Thread starter ajm
- Start date
shaowu459
Well-known Member
- Joined
- Apr 26, 2018
- Messages
- 687
- Office Version
- 365
- Platform
- Windows
| Book1.xlsx | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | 10/04/2017 15:00 | 10/04/2017 | ||
| 2 | 10/05/2017 14:00 | 10/05/2017 | ||
| 3 | 10/11/2017 15:00 | 10/11/2017 | ||
| 4 | 10/12/2017 14:00 | 10/12/2017 | ||
| 5 | 10/18/2017 03:00 PM | 10/06/2018 | ||
| 6 | 10/18/2017 03:00 PM | 10/06/2018 | ||
| 7 | 10/19/2017 01:00 PM | 10/07/2018 | ||
| 8 | 10/19/2017 02:00 PM | 10/07/2018 | ||
| 9 | 10/19/2017 03:00 PM | 10/07/2018 | ||
| 10 | 10/23/2017 07:00 AM | 10/11/2018 | ||
Sheet4 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1:B10 | B1 | =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)) |
Upvote
0
Similar threads
- Solved
