View Single Post
 
Old 09-27-2014, 12:23 AM
Nisio07 Nisio07 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Sep 2014
Posts: 21
Nisio07 is on a distinguished road
Default Macro to format unpredictable data

I have attached a sample file of data I download on a weekly basis. It generated total time spent by various students. Unfortunately, the time is unpredictable. All fields come trailing with the words "hours or "minutes". These can be easily removed but it what them happens which is nuts! the time can be formatted as mm:ss, hh:mm:ss or yy dd mm hh:mm:ss. If you view the attached you will see more clearly. (Column C is a simple value paste of column b with the word " minutes" removed)

Is there any macro that will cater for all these possible formats and just give me a column contain the time value in minutes? BTW, there really is no way of altering the format of the incoming data. I wish to rank students in time spent so this need the time data in comparable, numeric format.

Any help greatly appreciated.

Regards

Nisio
Attached Files
File Type: xlsx msof query.xlsx (8.4 KB, 12 views)
Reply With Quote