Date from database is in TEXT format "7/22/2020 America/Chicago" How do I convert that to a date format mm/dd/yyyy. If that's not possible, I can use a text format of mm/dd/yy. I tried the MID, LEFT, RIGHT functions to chop off the America/Chicago but that didn't work since the dates don't have the same number of characters in the same positions etc. Thanks for your help!
-
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.
Change Date Format
- Thread starter Danny318
- Start date
DRSteele
Well-known Member
- Joined
- Mar 31, 2015
- Messages
- 2,640
- Office Version
- 365
- Platform
- Windows
Let's try this:
| MrExcel posts18.xlsx | ||||||||
|---|---|---|---|---|---|---|---|---|
| D | E | F | G | H | I | |||
| 3 | example | space | left of space | converted | singlecell | singlecell date format | ||
| 4 | 7/1/2020 America/Chicago | 9 | 7/1/2020 | 44013 | 44013 | 7/1/2020 | ||
| 5 | 7/22/2020 America/Chicago | 10 | 7/22/2020 | 44034 | 44034 | 7/22/2020 | ||
| 6 | 12/2/2020 America/Chicago | 10 | 12/2/2020 | 44167 | 44167 | 12/2/2020 | ||
| 7 | 12/29/2020 America/Chicago | 11 | 12/29/2020 | 44194 | 44194 | 12/29/2020 | ||
Sheet39 | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E4:E7 | E4 | =SEARCH(" ",D4) |
| F4:F7 | F4 | =LEFT(D4,E4-1) |
| G4:G7 | G4 | =DATEVALUE(F4) |
| H4:H7 | H4 | =DATEVALUE(LEFT(D4,SEARCH(" ",D4)-1)) |
| I4:I7 | I4 | =H4 |
Upvote
0
Similar threads
