Excel: convert unusual minute format into numbers

Question

I want to analyze data from a application in Excel 2010. The application stores worked hours in a unusual way:

• 15 = 15 minutes
• 30 = 30 minutes
• 45 = 45 minutes
• 100 = 60 minutes / 1 hour
• 115 = 75 minutes / 1 hour 15 minutes
• 130 = 90 minutes / 1 hour 30 minutes
• ...
• 200 = 120 minutes / 2 hours
• ...

My desired output are minutes as numbers (15, 30, 45, 60, 75, ...), not in time format. I am looking for a formula to achieve this.

Answer 1

You should be able to do this with a combination of integer math and modulus. INT(A1/100) will give the Hours, MOD(A1,100) will give the minutes.

       =INT(F3/100)*60+MOD(F3,100)
100    60
30     30
130    90