SOLVED

Small problem using time conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-204916%22%20slang%3D%22en-US%22%3ESmall%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204916%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20been%20using%20a%20time%20conversion%20for%20my%20mp3s%20and%20for%20some%20reason%20it%20has%20quit%20and%20I%20have%20no%20clue%20as%20to%20why.%26nbsp%3BHere's%20the%20conversion%20formula%3A%26nbsp%3B%3DTIME(0%2CINT(I1829*24)%2CMOD(I1829*24%2C1)*60).%3C%2FP%3E%3CP%3EThe%20conversion%20is%20done%20when%20column%20%22I%22%20is%20hours%20and%20minutes%20and%26nbsp%3Bcolumn%20%22G%22%20is%20hours%2C%26nbsp%3Bminutes%20and%20seconds.%20I've%20gotten%20down%20to%20row%201831%20and%20I%20use%20FILL%26gt%3BDOWN%20to%20do%20the%20conversions%20and%20it%20has%20worked%20up%20until%20now.%20Once%20I%20try%20the%20same%20FILL%26gt%3BDOWN%2C%20the%20row%20adjacent%20to%20the%20empty%20column%2C%20it%20just%20copies%20the%20number%20from%20the%20adjacent%20row%20containing%20the%20conversion.%20All%20columns%20are%20formatted%20the%20same%20as%20the%20previous%20rows%20ie%3Ah%3Amm%3Ass%20in%20column%20%22G%22%20and%20h%3Amm%20in%20column%20%22I%22.%20I've%20attached%20a%20screen%20shot%2C%20hopefully%2C%20to%20explain%20a%20little%20bit%20better.%20The%20data%20is%20brought%20in%20using%20%22csv%22%20and%20I'm%20using%20Office%20365.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-204916%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1685173%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1685173%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F109073%22%20target%3D%22_blank%22%3E%40thomas%20foley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205777%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205777%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Thomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%20for%20the%20feedback.%20Yes%2C%20pictures%20and%20even%20more%20better%20sample%20files%20without%20sensitive%20information%20help%20to%20answer%20a%20lot.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205622%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205622%22%20slang%3D%22en-US%22%3EJust%20to%20let%20you%20know%20that%20the%20conversion%20is%20working%20again.%20I%20just%20entered%20some%20data%20and%20tried%20the%20conversion%20and%20it%20worked.%20I've%20done%20this%20conversion%20for%20over%203000%20mp3's%20and%20it%20worked%2C%20why%20it%20stopped%20working%2C%20I%20have%20no%20idea.%20Trying%20to%20explain%20what%20you're%20trying%20to%20do%20and%20put%20it%20in%20as%20simple%20terms%20as%20possible%20so%20others%20can%20understand%20is%20rather%20challenging%20especially%20if%20you're%20not%20proficient%20with%20the%20program%20as%20others%20may%20be.%20I%20appreciate%20the%20help%20but%20the%20next%20time%20I%20have%20a%20problem%20I'll%20use%20more%20attachments.%20Pictures%20are%20worth%20a%20thousand%20words%2C%20especially%20when%20you're%20trying%20to%20explain%20a%20problem.%20Thanks%20again.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205046%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205046%22%20slang%3D%22en-US%22%3EOOps%2C%20made%20a%20mistake%20it%20copies%20the%20result%20if%20I1%20down%20to%20I1850%20not%20K1%2C%20not%20paying%20attention.%20All%20cells%20are%20highlighted%20as%20you%20mention%20in%20your%20last%20post%20and%20I%20use%20Fill%26gt%3Bdown.%20This%20has%20only%20happened%20ever%20since%20I%20went%20to%20Office%20365.%20I%20still%20have%20my%20Office%202013%20on%20my%20other%20computer%20and%20I%20have%20no%20problems%20with%20the%20conversion.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205045%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205045%22%20slang%3D%22en-US%22%3EThat's%20exactly%20what%20I've%20been%20saying%20but%20Excel%20won't%20do%20it%2C%20it%20just%20copies%20what%20is%20inside%20K1%20down%20to%20k1850.%20It%20won't%20do%20the%20conversion%20and%20I%20have%20no%20idea%20as%20to%20why.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205044%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205044%22%20slang%3D%22en-US%22%3E%3CP%3EThomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOkay%2C%20let%20say%20we%20have%20K1%3AK1850%20with%20data%20and%20in%20I1%20formula%20to%20convert%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20263px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36147i5976863EB6E80C69%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ELet%20select%20now%20I1%3AI1850%2C%20end%20of%20selection%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20545px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36150i0EACE6F9EE38ED71%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EKeeping%20the%20selection%20in%20ribbon%20Fill-%26gt%3BDown%20(or%20Ctrl%2BD)%3C%2FP%3E%0A%3CP%3EThat's%20all%2C%20the%20result%20is%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20541px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F36149iC72F0EC86FD6D729%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESame%20result%20will%20be%20if%20drag%20I1%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOther%20words%2C%20that's%20the%20same%20as%20you%20tried%20to%20do%2C%20but%20what%20exactly%20was%20wrong%20in%20your%20steps%20is%20not%20clear%20from%20your%20descriptions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205035%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205035%22%20slang%3D%22en-US%22%3ELets%20make%20this%20real%20simple.%20If%20Column%20k%20houses%201850%20rows%20of%20hours%20and%20minutes%20how%20would%20I%20convert%20those%20rows%20to%20minutes%20and%20seconds%20without%20having%20to%20type%20a%20formula%201850%20times%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-205030%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-205030%22%20slang%3D%22en-US%22%3E%3CP%3EThomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20formula%20to%20use%20it%20doesn't%20matter%20from%20conversion%20point%20of%20view%2C%20both%20give%20exactly%20the%20same%20result%2C%20just%20the%20second%20one%20is%20much%20shorter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20for%20the%20conversion%2C%20do%20I%20understand%20correctly%20how%20do%20you%20use%20Fill%20Down%3C%2FP%3E%0A%3CP%3E-%20you%20stay%20on%20G1%20for%20which%20formula%20without%20absolute%20references%20correctly%20converts%20hours%20in%20I1%20into%20munutes%20in%20G1%3B%3C%2FP%3E%0A%3CP%3E-%20you%20select%20cells%2C%20let%20say%20from%20G1%20to%20G2000%20(G1%3AG2000)%2C%3C%2FP%3E%0A%3CP%3E-%20in%20ribbon%20Home-%26gt%3BFill-%26gt%3BDown%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20result%20no%20one%20cell%20is%20converted%20correctly%2C%20or%20all%20correctly%20but%20G1832%20(as%20on%20your%20picture)%20or%20whar%3F%20Which%20exctly%20formulas%20do%20you%20see%20in%20G1831%20and%20G1832%20aftert%20that%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20other%20scenario%20when%20you%20drag%20G1%20down%20to%20G2000%20(I%20guess%20by%20left%20mouse%20click)%20is%20it%20the%20same%20result%2C%20and%20where%20and%20why%20do%20you%20press%20Shift%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204986%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204986%22%20slang%3D%22en-US%22%3EIf%20i%20convert%20the%20first%20number%20to%20minutes%20and%20seconds%2C%20I%20should%20be%20able%20to%20take%20that%20conversion%2C%20I'll%20take%20your%20suggestion%20and%20use%20it%20for%20the%20formula%2C%20high%20lite%20the%20conversion%20formula%20in%20the%20column%2C%20use%20shift%20and%20drag%20down%20to%20maybe%20I1850%20and%20use%20FILL%26gt%3BDOWN.%20The%20conversion%20formula%20should%20fill%20all%20the%20columns%20with%20the%20correct%20conversion.%20Instead%20it%20is%20only%20COPYING%20the%20high%20lighted%20conversion%20down%20to%20I1850%2C%20for%20example%2C%20if%20the%20first%20number%20was%20converted%20to%206%20minutes%2020%20seconds%20by%20the%20formula%20the%20following%20number%2C%20that%20being%20different%20than%20the%20first%2C%20should%20be%20converted%20to%20whatever%20minutes%20and%20seconds%20is%20reported%20in%20column%20%22I%22%2C%20instead%20it's%20COPYING%206%20minutes%20and%2020%20seconds%20to%20the%20next%20row%20instead%20of%20converting.%20It%20seems%20as%20if%20Excel%20is%20telling%20me%20to%20write%20the%20formula%20for%20each%20individual%20row%20in%20order%20to%20convert%20the%20time%20in%20each%20case.%20You%20shouldn't%20have%20to%20do%20that.%20I%20never%20had%20any%20problem%20in%20Office%202013%2C%20only%20since%20I've%20converted%20to%20Office%20365%20have%20I%20had%20this%20problem.%20WHY%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-204947%22%20slang%3D%22en-US%22%3ERe%3A%20Small%20problem%20using%20time%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-204947%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Thomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20not%20clear%20why%20Fill%20Down%20doesn't%20work%2C%20perhaps%20you%20may%20share%20your%20file%20removing%20all%20information%20but%20time%20columns.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20for%20transforming%20you%20may%20use%3C%2FP%3E%0A%3CPRE%3E%3DI1829%2F60%3C%2FPRE%3E%0A%3CP%3Einstead%20of%20TIME()%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 is done when column "I" is hours and minutes and column "G" is hours, minutes and seconds. I've gotten down to row 1831 and I use FILL>DOWN to do the conversions and it has worked up until now. Once I try the same FILL>DOWN, the row adjacent to the empty column, it just copies the number from the adjacent row containing the conversion. All columns are formatted the same as the previous rows ie:h:mm:ss in column "G" and h:mm in column "I". I've attached a screen shot, hopefully, to explain a little bit better. The data is brought in using "csv" and I'm using Office 365.

10 Replies

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.

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?

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?

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?

Thomas,

 

Okay, let say we have K1:K1850 with data and in I1 formula to convert

image.png

Let select now I1:I1850, end of selection:

image.png

 

Keeping the selection in ribbon Fill->Down (or Ctrl+D)

That's all, the result is

image.png

 

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.

That's exactly what I've been saying but Excel won't do it, it just copies what is inside K1 down to k1850. It won't do the conversion and I have no idea as to why.
OOps, made a mistake it copies the result if I1 down to I1850 not K1, not paying attention. All cells are highlighted as you mention in your last post and I use Fill>down. This has only happened ever since I went to Office 365. I still have my Office 2013 on my other computer and I have no problems with the conversion.
best response confirmed by thomas foley (Occasional Contributor)
Solution
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.

Hi Thomas,

 

Thank you for the feedback. Yes, pictures and even more better sample files without sensitive information help to answer a lot.