Date time US to UK format conversion problems
I have an Excel file I'm importing which has a date/time in US format (month/day/year hour:min:sec AM/PM) which I need to convert to UK format (day/month/year hour:min:sec in 24 hour format).
I've done some reading and read that a 'Date to Nominal' then 'Nominal to Date' conversion back was the way forward.
I've managed to get the Date to Nominal conversion working, so my US date is output as a nominal in the correct UK format date (ie. my month and day are switched correctly, so Jan 8 becomes 1st August - which is what I need to happen) with the time in 24 hour format.
However, on the conversion from Nominal back to date RapidMiner is doing some odd things that stuff my results completely.
1. Regardless of the date format I specify in the conversion parameter, it shows my dates as AM/PM in 12 hour format not the 24 hour format in the Nominal date
2. It subtracts 1 hour from the time, so a date that is 11:35:12 AM is converted back as 10:35:12 AM
This is maddeningly frustrating! Please can someone have a look at the attached data file and conversion flow and give me a hint as to where this is going wrong? It should be so simple and I've read the help file for Nominal to Date over and over now and can't see the problem.