Forum Discussion

Copper Contributor

Jun 16, 2018

Solved

Small problem using time conversion

I've been using a time conversion for my mp3s and for some reason it has quit and I have no clue as to why. Here's the conversion formula: =TIME(0,INT(I1829*24),MOD(I1829*24,1)*60). The conversion i...

Formulas & Functions

Need Help

thomas foley
Jun 19, 2018
Just to let you know that the conversion is working again. I just entered some data and tried the conversion and it worked. I've done this conversion for over 3000 mp3's and it worked, why it stopped working, I have no idea. Trying to explain what you're trying to do and put it in as simple terms as possible so others can understand is rather challenging especially if you're not proficient with the program as others may be. I appreciate the help but the next time I have a problem I'll use more attachments. Pictures are worth a thousand words, especially when you're trying to explain a problem. Thanks again.

SergeiBaklan

Diamond Contributor

Jun 16, 2018

Hi Thomas,

That's not clear why Fill Down doesn't work, perhaps you may share your file removing all information but time columns.

By the way, for transforming you may use

=I1829/60

instead of TIME() function.

thomas foley

Copper Contributor

Jun 17, 2018

If i convert the first number to minutes and seconds, I should be able to take that conversion, I'll take your suggestion and use it for the formula, high lite the conversion formula in the column, use shift and drag down to maybe I1850 and use FILL>DOWN. The conversion formula should fill all the columns with the correct conversion. Instead it is only COPYING the high lighted conversion down to I1850, for example, if the first number was converted to 6 minutes 20 seconds by the formula the following number, that being different than the first, should be converted to whatever minutes and seconds is reported in column "I", instead it's COPYING 6 minutes and 20 seconds to the next row instead of converting. It seems as if Excel is telling me to write the formula for each individual row in order to convert the time in each case. You shouldn't have to do that. I never had any problem in Office 2013, only since I've converted to Office 365 have I had this problem. WHY?

SergeiBaklan
Diamond Contributor
Jun 17, 2018
Thomas,

Which formula to use it doesn't matter from conversion point of view, both give exactly the same result, just the second one is much shorter.

As for the conversion, do I understand correctly how do you use Fill Down

- you stay on G1 for which formula without absolute references correctly converts hours in I1 into munutes in G1;

- you select cells, let say from G1 to G2000 (G1:G2000),

- in ribbon Home->Fill->Down

As result no one cell is converted correctly, or all correctly but G1832 (as on your picture) or whar? Which exctly formulas do you see in G1831 and G1832 aftert that?

In other scenario when you drag G1 down to G2000 (I guess by left mouse click) is it the same result, and where and why do you press Shift?
- thomas foley
  Copper Contributor
  Jun 17, 2018
  Lets make this real simple. If Column k houses 1850 rows of hours and minutes how would I convert those rows to minutes and seconds without having to type a formula 1850 times?
  - SergeiBaklan
    Diamond Contributor
    Jun 17, 2018
    Thomas,
    
    Okay, let say we have K1:K1850 with data and in I1 formula to convert
    
    Let select now I1:I1850, end of selection:
    
    Keeping the selection in ribbon Fill->Down (or Ctrl+D)
    
    That's all, the result is
    
    Same result will be if drag I1 down.
    
    Other words, that's the same as you tried to do, but what exactly was wrong in your steps is not clear from your descriptions.