Jun 15 2018
07:14 PM
- last edited on
Jul 31 2018
08:28 AM
by
TechCommunityAP
Jun 15 2018
07:14 PM
- last edited on
Jul 31 2018
08:28 AM
by
TechCommunityAP
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.
Jun 16 2018 05:20 AM
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.
Jun 16 2018 06:25 PM
Jun 17 2018 06:37 AM
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?
Jun 17 2018 08:22 AM
Jun 17 2018 09:11 AM
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.
Jun 17 2018 09:41 AM
Jun 17 2018 09:57 AM
Jun 19 2018 06:04 AM
SolutionJun 19 2018 10:37 AM
Hi Thomas,
Thank you for the feedback. Yes, pictures and even more better sample files without sensitive information help to answer a lot.
Jun 19 2018 06:04 AM
Solution