Time Question

[Abhinav Shori]

Hi All,
I have time as 2:19, the format is [m]:ss and it is a CSV file. Now when I try to copy it into an excel file it shows as 2:19:00 in general time format or 139:00 in [m]:ss but I would like it to stay 2:19 in [m]:ss format. How can I do that?

Thanks in advance

 

[Ajesh]

Hi Abhinav,

After copying, you can divide the values by 60 for e.g. in B2 =A2/60 and then paste as values in cell A2 with format [m]:ss

Thanks.

 

[Abhinav Shori]

Thanks Ajesh, but is there a way to convert the time, say if I have 1 hour 12 mins i.e 1:12:00 to just show 0:1:12 i.e 1 minute and 12 secs without any formulas?

 

[Ajesh]

Unfortunately Excel tries to help you by guessing the data format based upon your data while coping/importing and formats the cell accordingly which is mostly helpful. But sometimes this is not a help but problem. You can try formating the cells as "Text" rather than genral and copy value to those. But they will be stored as Text value and not Time value and you may not be able to perform calculations on them in current form.

Thanks

 

[Khalid NGO]

Thanks Ajesh, but is there a way to convert the time, say if I have 1 hour 12 mins i.e 1:12:00 to just show 0:1:12 i.e 1 minute and 12 secs without any formulas?

Hi Abhinav,

Try if it works for you:
=TEXT(A2,"[h]:mm:ss")/60


EIDT:
Plz ignore the above.
I thought you were looking for formula.

 

[Syedali]

hi,

Please attach a sample file, so we can try this.