NZExcelUser
New member
- Joined
- Oct 11, 2017
- Messages
- 1
- Reaction score
- 0
- Points
- 0
I have imported data in an XML file into EXCEL that doesn't seem to be formatting correctly as a Date and appears to be remaining as 'General' formatting. The date and time strings are shown in US format (m/d/yy) by default and are either "7/3/17 3:40 AM" or "7/13/17 10:13 AM". When I use the DATEVALUE formula on the shorter dates like 7/3/17 it pulls the correct European date of 3rd July 2017 out as 03/07/2017 but the DATEVALUE function doesn't recognise the 7/13/17 dates which have one extra digit on the middle day numbers.
I have tried to use the MID and LEFT functions to pull out the date numbers but the formula I have been using doesn't cope when the extra digit is added in for say 13th of the month vs 3rd of the month (or similarly the 6th month of the year vs the 11th month of the year).
The formula I have been using is :-
=DATE(MID('XML Combined Account Statement'!D17,5,2)+2000,LEFT('XML Combined Account Statement'!D17,1),MID('XML Combined Account Statement'!D17,3,1))
The format of the data is shown below so I can't use the RIGHT command either as the number of digits in the TIME data also changes. Wondering if anyone has any ideas on where I am going wrong with this?
I have tried to use the MID and LEFT functions to pull out the date numbers but the formula I have been using doesn't cope when the extra digit is added in for say 13th of the month vs 3rd of the month (or similarly the 6th month of the year vs the 11th month of the year).
The formula I have been using is :-
=DATE(MID('XML Combined Account Statement'!D17,5,2)+2000,LEFT('XML Combined Account Statement'!D17,1),MID('XML Combined Account Statement'!D17,3,1))
The format of the data is shown below so I can't use the RIGHT command either as the number of digits in the TIME data also changes. Wondering if anyone has any ideas on where I am going wrong with this?
| |||||||
7/5/17 3:32 PM | |||||||
7/3/17 1:47 AM | |||||||
7/7/17 6:31 AM | |||||||
7/3/17 3:40 AM | |||||||
7/6/17 11:07 AM | |||||||
7/3/17 4:57 AM | |||||||
7/13/17 10:13 AM | |||||||
7/5/17 1:51 PM | |||||||
7/12/17 11:00 PM | |||||||
7/5/17 2:00 PM | |||||||
7/7/17 5:47 AM | |||||||
7/5/17 2:00 PM |