Convert time (text) to Excel formatted Time

Question

I have a list of durations in minutes used to log calls and I want to use Excel to format them and do some calculations. However, these are just exported as table and when I attempt to bring them into Excel, the cells won't format correctly.

An example below:

01:00 is entered as the number of minutes and seconds

However, when I try to use Excels cell format it turns it into 01:00:00 as in hours, minutes, seconds (hh:mm:ss).

Now I want to convert it to this as I also have the cost per minute which I then need to multiply by this time. To clarify further as to why it needs to be in this format, is because i want to use the follow query to get the total cost per minute.

=(HOUR(H4)*60*I4)+(MINUTE(H4)*I4)+(SECOND(H4)/60*I4)

Can anyone help with how to get Excel to recognise this in mm:ss format.

just getting this straight, this basically boils down to you want to display the time in only minutes and seconds, but other than that everything else works? That formula you have at the end should work just fine assuming that I4 is your price per minute and H4 is your time saved in an excel date/time format. — Forward Ed
the cell containing your Time, lets assume H4, when you use the formula =ISNUMBER(H4) what do you get? — Forward Ed
Your title is misleading as there is no text time to convert since your time is already a number. — Forward Ed
Try dividing the time by 60 this will convert the erroneous hh:mm into mm:ss — Gary's Student
Ok, that was ridiculously easy and works perfectly. Embarrassed If you want to put it as an answer will mark as correct :) — Seán McCabe

Xanderak Xanderak · Accepted Answer · 2016-06-03T03:21:51

Try this workaround. Format A column to be "text" type. Paste your data into column A. Format B column to be "custom" type, choose "mm:ss". Copy below formula down column B.

A1: 01:00

B2: =0+("00:"&A1)

B column will display and function as you desire.

Convert time (text) to Excel formatted Time

1 Answers