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, 10 views)

First, I would ask that the data be generated in a more suitable format. Have all data in the time column exported as hh:mm:ss with no succeeding text.

But you can work with the data on hand until then by entering a conversion equation to the right (here for the data on line 2):

=LEFT(B2, FIND(" ",B2)-1)/IF(RIGHT(B2,5)="Hours",1,60)

See attached. I formatted the column C as h:mm:ss so the complete time is shown.

Attached Files

msof query.xlsx (9.0 KB, 8 views)

Gebobs is described as "competent performer".. That is lie ...; Better words would be GENIUS... EXPERT .. EXCELLENT EXCEL-LER ... Change that tag line quickly... Many thanks ... Brilliant non-macro solution...