And transforming this data back to its native formatting takes some fancy footwork in Excel.
As soon as you export the data you lose that beautiful, intuitive formatting and end up with something that makes no sense whatsoever. So when you export to Excel you’d think that formatting would follow. It follows an easy mm:ss format, with m representing minutes and s representing seconds. Many of the reports in Google Analytics show you either time on site or time on page, depending on the report. Solution: Run a simple formula in Excel to convert, then apply number formatting as follows. Not trying to imply it's the right tool/approach for all cases, just that it can serve as the more generalized approach for more involved tasks.Problem: I want to show Time on Site the way Google Analytics does, but it shows up in Excel as number of seconds when I export.
When I dump 1000 folks' race reports with five times each, however, rather than going back and manually adjusting 5000 data fields into three fields each, I just spend 30 seconds modifying the data with some creative dividing and I'm done. (Well, almost, I use two fields and ll put in 127 minutes rather than 2 hours and seven minutes, but materially the same. It has alwas just seemed so much easier to me to enter the time in 3 columns. I understand how Excel thinks regarding time - I just find it a PITA. The two posts above mine can work great for individual workouts for certain needs but I still think there's value in understanding the underlying logic Excel uses for time measurement. The point is, once you understand the implications of how Excel thinks about time, all the rest of this becomes pretty intuitive - just understand how Excel is interpreting the data and modify by 24 or 60 as appropriate to convert to the units you need. I'm not a pro, but I am the guy who still gets asked Excel questions by peers who've spent 60+ hours a week for years pounding out office documents.
I tend to get carried away talking about Excel so not sure if I've over-explained it or just confused people so I'll stop there. To convert that to the appropriate scale, you'd divide by 24 (hours in day" and by 60 (minutes in an hour ). well, now Excel is going to think you did something for 30 days. Similarly, if you did a 30 minute run and just typed in 30.
Now that it's on the right scale, if you divide it by the distance it'll still look right. Now it'll be thinking of your data in the right scale, 0.013 days. Once you see that, you know that excel has misinterpreted your "MM:SS" entry as "HH:MM." How do you convert hours to minutes and minutes to seconds? No problem! Just divide your data by 60. If you put in your time as "18:15" excel will probably interpret that as "6:15 PM." Change that to "number" format and it'll show up as 0.760, i.e., just over 3/4 of the way through the day. Once you really understand that, the rest is easy. Put 3.25 in a cell and as a date it'll say "1/3/00," but put it in time format and it'll show up as "6:00 AM" because 6:00 AM is 1/4 of the way through the day. " So if you put "3" in a cell and change it to M/D/Y format it'll say "1/3/00." If you put it in time it'll show up as midnight. Under the 'teach a man to fish' guise I'm going to go into a bit of detail here.įirst of all, excel thinks (not displays, but thinks ) of all time/date information in units of "days since.
0 kommentar(er)
