I have a report that I export into Excel (see attached). It comes from Global Shop Solutions > Crystal Reports Viewer and is a report on time spent in work centers. I've cleaned and organized the data, now I just need to translate and calculate accurately.
You'll notice in the attached file, columns I and J are in decimal format. In columns M and N, I've manually typed in some examples what it should be. I used find and replace (period to colon with Ablebits Tools), then I used the [h]:mm formatting tool. That worked for most of them. But inevitably, I still get some values that translate incorrectly. Most notably, even values, such as those in row 99 (20.00 hours, zero minutes, etc) would translate over as 480:00. (It must be thinking 20*24 hours somehow?)
I have nearly 200,000 rows of data. Does anyone know of a method for doing this quickly and accurately? For reference, if we add up all the Est hours and Act hours for the 5X01 W.C. (column A), they should total 8,366:14 and 4,114:42 respectively.
Thank you in advance!
Hi Loren,
Enter this formula in M2 to transform the estimated hours:
=INT(I2)/24+MOD(I2,1)*100/1440
Copy it all the way down and one column to the right and you'll get the hh:mm values you need. Custom format both columns M and N as [h]:mm
Riny
You could also use:
=TEXT(I2*100,"00:00")+0
with the same [h]:mm formatting
Riny: Everything calculates and no errors were returned. Thank you! However, when there is a negative time value, it calculates incorrectly.
Velouria: I get errors when there is a negative time.
In the original file, I had to scroll down to row 649 before I found some negatives. It's rare but sometimes a manager has to manually subtract time from worker's time card. So that's when it shows up as a negative value.
I tried putting a negative sign in the formulas, but that didn't work. Other than negative number
s, it's working great! One step closer!
Yeah, forgot to mention that. Noticed your data had one negative in it. Assumed that is was a mistake of some sort, as there really isn't something like "negative time". Looking at your screen shot I can only wonder how something estimated not to take place (i.e zero seconds) will actually take minus 1 second.
Can you clarify that?
You can set-up Excel to use the 1904 date system. It allows you to display negative numbers in a time format. Though, it shifts all your dates by 4 years.
I can try to clarify. It will likely be a long phone call with Global Shop Solutions (GSS) . (Quick fact: I'm still elementary in my data analyst roll. But those are the exact people I talk to at GSS - data analysts and scientists. Trying to figure out a lot of their processes!)
My only thought on the negative time right now is that a manager has to go in sometimes and manually adjust the employee's time card, due to clocking in/out of the wrong job or work center.
I will look into the 1904 date system. Thank you!
I've run into a problem with this again. Maybe it was happening all along and I didn't realize it. (Though I sure hope not!!) All of sudden, I've noticed that it's just not converting the numbers as I desire.
To reiterate: I export from Global Shop Crystal Reports Viewer to Excel. Right off the bat, I have a problem of it turning a colon into a decimal. The steps above helped resolve that and it's what I've been doing for a while. Up until now.
You'll see in the attached file, ROW 10 is a good example of the way things should be. (Looking primarily at columns H, I, K and L, Estimated & Actual hours, and their respective conversions in K and L). In row 10, it takes 7.20 and converts it to 7:20 (seven hours & 20 minutes; also converts 3.25 to 3:25 correctly) Rows 4 and 14 are also correct.
Alas, most of them are NOT converting correctly. This totally caught me off guard. I double checked my cell format [h]:mm and that looks good. I double checked everything mentioned above and it is also correct so far as I can tell.
Any suggestions????
Here is my attachment again in case it didn't come through.
Didn't see you latest message until now.
Perhaps you can start by clarifying again what we are looking at. On Row 10 you say 7.20 and 3.25 correctly translates to 7hrs 20min and 3hrs 25min. Following that principle, I would say that most conversions are correct. But, perhaps where you get unwanted results is where the decimal part is greater than 0.59.
And the fact this this is possible makes me believe that we are looking at decimal representations of duration. For example, row 3, 0.9hrs should translate to 54 minutes. With the formula that you have used so far, 0.9 translates to 0hrs 90min (i.e. 1hr 30min).
Similar for row 6: 15.60 translates to 15hrs 60min equaling 16hrs. But perhaps this should be 15hrs 30min.
If we are looking at decimal hours, 7.20 should be 7hrs 12min (i.e. 7.2/24 = 0.3, formatted as [h]:mm ).
I can't tell what's correct. You?
I just followed technology 101 and did a fresh re-export of the data. I then spent 10 minutes simply looking and comparing numbers.
Looks like it's all back to good again!
My only guess is the data got corrupted somewhere along the way. Maybe during a copy/paste or something.
Okay to disregard this latest hiccup! 🙂
