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

msof query.xlsx (8.4 KB, 12 views)