Time Format

[coolzero]

Hi,

I need again excel formula assistance and i know you guys always do.

Attached is my sample file. I need to have a formula wherein the "Duration" column will be converted to decimal numbers. The problem is, there are numbers that supposed to be in mm:ss format but is read as hh:mm in the data. So, the other time in which in hh:mm:ss where treated as the same with mm:ss.

I need to have a distinction that those seen mm:ss should not be treated as hh:mm.


Looking forward again guys!


Thanks.
Marnie

[coolzero]

Btw, the reason for this is to get those done less than 4hours.

[Phuocam]

Try ...

Please Login or Register  to view this content.

Use: =ConvertTime(B2)

[coolzero]

hi, i am not that familiar with vb or macro. i already tried defining the len of the duration but unfortunately they have the same since the format were the same. it's just the format on how it shows that looks like 5 characters.


Thanks

[AlKey]

Maybe this
Enter in E2 and copy down

Please Login or Register  to view this content.

[coolzero]

Thanks AlKey, but already tried that. The problem is there are time in hh:mm:ss that ends in 00sec.

Thanks

[vlady]

can you try this one kabayan.

You have 2 formatting
h:mm:ss -> this is "D8" for the formula =cell("format",B2)
hh:mm -> this is "D9" for the formula =cell("format",B2)
so if cell format is D9 convert it to mm:ss using Time function otherwise just get the original date from the cell reference

Please Login or Register  to view this content.

***I just don't know the effects on the other formatting you have in your data.
Hope this is what you want.

[FDibbins]

there are numbers that supposed to be in mm:ss format but is read as hh:mm in the data.

How would we know which is which, they all look valid to me?