CODEWITHSUNDEEP
exceltimeformat
Seán McCabe
Seán McCabe

Reputation: 911

Convert time (text) to Excel formatted Time

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.

Upvotes: 1

Views: 576

Answers (1)

Xanderak
Xanderak

Reputation: 58

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.

Upvotes: 1

Related Questions