I am working to create a charting tool in excel that I can present to my leadership once a month about our render farm resources.
I am working with a table of all the completed job stats from deadline.
The issue I run into is when I have various units for one type of entry.
For example when deadline records data; it uses Bytes, MB and GB.
This requires a lot of work in excel to parse them all into one unit type. I have 2 issues. I am struggling to get a nested statement to function reliably in excel, and with the size of this table it takes a bit of time to process everything and I would rather keep the equations as simple as possible as they are sometimes duplicated 20,000 times.
Here is an example of data from the table.
Is there a way to get everything to record in decimal MB or decimal GB?
Sample of the data that is problematic.
0.000 Bytes
2.004 GB
126.621 MB
126.621 MB
0.000 Bytes
126.621 MB
126.621 MB
26.367 MB
126.621 MB
126.621 MB
126.621 MB
126.621 MB
26.363 MB
26.371 MB
149.758 MB
149.762 MB
132.086 MB
132.086 MB
26.367 MB
1.268 GB
1.268 GB
I have to do a similar conversion for times, but the recording of that is consistant in the table so it was a simple equation to convert all times into minutes.
=IF(Source!AE2=0,0,((MID(Source!AE2,1,2)*86400)+(MID(Source!AE2,4,2)*3600)+(MID(Source!AE2,7,2)*60)+(MID(Source!AE2,10,2)))/60)
thanks,
Well, this is a heck of a problem.
I did some cursory digging online and I’m a bit amazed that Excel doesn’t just natively support file sizes like it does time.
I’m no excel wizard, but the formula should be something like:
if ends in kb:
multiply number part by 1024
if ends in mb:
multiply number by 1048576
if ends in gb:
multiply number by 1073741824
if ends in tb:
multiply number by 1099511627776
I’ll open a ticket for us to put data in the CSVs in a simpler way (every time span in seconds, every file size in bytes) and talk to the devs about it.
Awesome,
like i said, the time issue was easy. but querying the end of the string seems tricky. Excel may have a tool for doing it but I don’t know what it would be.
Since the size of the suffixes vary in length (Bytes, GB and MB) and the prefix varries in length depending on the number its a challenge.
My buddy wrote a VBA macro that does it but its a work around at best
Yes please. Also can that be done when you copy the tasks out of the Tasks window? Formatting time with Days:Hours:Minutes:Seconds doesn’t play nice with Excel since excel doesn’t expect a “days” duration. Just giving all stats in all copy/paste and CSV data as straight up seconds and MBs would be very helpful.
here is the formula I made to get the days min seconds to work with excel
=IF(Source!AE2=0,0,((MID(Source!AE2,1,2)*86400)+(MID(Source!AE2,4,2)*3600)+(MID(Source!AE2,7,2)*60)+(MID(Source!AE2,10,2)))/60)
but doing it this way means I am keeping all the data in my workbook twice, once to house the data and once to house the formatted data, not ideal but it works.
In our usual fashion, our intern Morgan managed to crank this fix out in a day.
It’ll be in the next beta release of 6.2, and we’re not limiting this change to CSV export. Any data copied to the clipboard from a view will also now use the base values.
Percentages will be expressed in a decimal from 0 to 1.
Edit:
Correction! Percentages are from 0 to 100! We actually store them in the Qt data model as integers, so you’re getting the raw data straight from the source 