Converting decimal into time

Greetings all, this is my first post in here.

I'm struggling with maintaining integrity of some data. I export from Global Shop Crystal Report Viewer into Excel. Unfortunately, I run into a problem right off the bat. What should be kept as time, turns into a decimal (example, hh:mm, can range from 00:01 to 25:31 or more; these values turn into 0.01 and 25.31, rather than maintaining the colon. The numbers/digits are correct; it just loses the colon. Of course, find/replace doesn't work!).

This was solved for a while, with an answer from MyOonlineTrainingHub. (You'll see in the attached file I was using INT and MOD with arithmetic to convert.) Recently, I noticed it hiccupping more frequently. In the file, rows 4, 10, and 14 are examples of how things should be. Looking at columns H, I, K and L. For instance, in row 10, 7.20 converts to 7:20, and 3.25 to 3:25. This is how it all should be! Keep the numbers, just get that decimal into a colon and format with [h]:mm

I've double checked things too many times now, if you know what I mean Had me stumped yesterday. Does anyone know a better solution than the INT and MOD stuff I have going on in there? For all I know, there's something staring me right in the face.

Note: I have about 200,000 rows of data of this stuff. I also have occasional negative time values (that should remain negative).

Thank you in advance!

Try this in K2:

=SUBSTITUTE(TEXT(H2,"0.00"),".",":")+0

Format as Custom [h]:mm and then drag to the right and down.

As for the negative values part, you can't have negative times.

You can display all of the values as text by removing the +0 at the end of the formula from post #2, and the negative would stay, but note that the cells will then be text values and not numbers/times.

Well shoot....that didn't quite work for me. Looked very hopeful at first! I think it works for more of the cells now than before.

What's super puzzling right now is that it seems to work just fine for some cells. But others, it's returning a completely different value. (see highlighted examples, yellow=wrong, green=correct)

Attachment 813779

Your attachment from post #4 is invalid. I cannot see it.

Putting the formula from post #2 into the workbook from post #1, essentially replaces the "." from columns H and I with ":" and converts them to time values.

For instances like cell H6 where you have 15.60, what time value should this be?
15:60 is not a valid time, which is why the formula converts this to 16:00.

I assume this is what you are referring to. What would you like for to show instead here?
15:60 (as a text value), 15:36 as a time value (since 0.6 hours = 36 mins), or something else?

Got into work this morning & followed technology 101: start over & do a fresh re-export of data. I used the original formulas (INT, MOD, etc) with format [h]:mm. Then I spent 10 minutes simply looking at and comparing numbers.

Of course, it looks like everything is back to the way it should be!

My only guess is data got corrupted somewhere along the way. Maybe during a copy/paste.

Okay to disregard at this point! (Looking for a place to mark this as resolved. Is that a thing on this forum?)