Link to home
Start Free TrialLog in
Avatar of MMacygin
MMacyginFlag for United States of America

asked on

Looking for a way to convert data in an Excel cell to consistent information.

I'm pulling reports from a Learning Management System on training completions.  While my team is working on modifying the backend information to deliver consistent data, I still need to report on current data coming out.  I have a column labeled "Training Duration".  The data currently being delivered can be in either Minutes or Hours.  An example of each is below:

0.75 Hours
30.00 Minutes

I'd like to examine the cell and convert all of them to A) Hours and B) a cell with just the actual time listed.  The above examples would then be turned into the following:


Is this a VB script problem?  Is there some formula in Excel that would allow me to do this?  Any assistance, suggestions or recommendations are appreciated.

An example of the data file  is attached. The column in question is highlighted.C--Users-06596.GLOBAL-Desktop-16010.xlsx
Avatar of Rgonzo1971


pls try
=IF(RIGHT(I2,5)="Hours",LEFT(I2,SEARCH(" ",I2)-1),IF(RIGHT(I2,7)="Minutes",TEXT(VALUE(LEFT(I2,SEARCH(" ",I2)-1))/60,"0,00"),""))

Open in new window

Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MMacygin


This was the solution I needed.  Handled the column flawlessly.  I so appreciate the quick response and solution.
Glad to know that the solution worked as per your expectations.