Recent content by P0lar

I think I've solved it using a couple of helper calcs, not sure if it can be done in the pivot table directly - here's what I've got to

Thanks Chirayu, Unfortunately the total isn;t always going to be 2000, I need it to recalculate as the total paid time in each site changes, or the number of rows of data I'm pivoting change. If I could add a calculated field of [LUNCH; WORKED] / sum[SITE; PAID] that would work.

I have been handed an excel sheet to fix which includes a pivot table: Location Time spent Time paid --Site A 1000 2000 ----Lunch 100 150 ----Break 200 150 ----Work 700 1700 --Site B ----etc. I...

Thanks for the help, I managed to get things working to the nearest minute (which should be good enough) using (15 - Right(tmFirst, 2) * 1 Mod 15) / 15 But I'll try a version with your suggestions as they will still work if there are any entries passed through with seconds included.

I have some times and need to know how many minutes there are to a 15 minute interval in vb. here's my code: stVal = (WorksheetFunction.RoundUp(CDate(tmFirst) * 96, 0)) - (CDate(tmFirst) * 96) where tmFirst is the time as a string, e.g. "07:00" the trouble is that although...

I have a line chart with around 75 line series on it. They all follow a similar trend but each line is different. My first 3 lines are the average and standard deviation for that point on the x-axis. I have used worksheet.selectionchange and created a new series on the chart so that as I pick...

Thanks for the pointers, got most of the records working now using the following monster... had to adjust as the number of directories in the path can vary and the string contains two paths, one of which isn't needed...

I have a list of xml command lines in excel (text fields) such as: -f /tv/ss/ex/dub/config/agedet_one2one_run.xml -s<s> -e<e> > /tv/ss/ex/dub/config/run_agedet.log 2>&1 -f /tv/ss/in/au/config/agedet_out_run.xml -s<s> -e<e> > /tv/ss/ex/dub/config/run_agedet.log 2>&1 -f...

Hi, I had a lovely macro which extracted data from one of our systems via ODBC. I was then upgraded my laptop to 64 bit Windows but kept 32 bit Excel and the connections have broken. Our IT team refuse to back out the change as 64 bit is the future... I've set up a new ODBC driver in sysWOW32...

Hi, I have a formula which creates a string including a superscript 0,1,2 or 3 - represented by chr(185), 186, 179 and 178: ¹0800-1500¹ I would like to pass this into an array in VB but can only fill the array with the #N/A "error 2042" for some reason, I suspect this may be due to the...

I have a pipe delimited .txt file export from another system which is likely to run close to 1 million rows when in full use, so importing to excel is risky and likely to slow the calculations etc. Each line shows how long a person spent on an activity on a given day, and there are about a dozen...

Can you add the reverse of the above code into a private sub workbook_beforeclose()macro to switch everything back to excels normal visibility?

Hi, multiply the answer by 24, and format the cell as "General" or "Number"

Hi Deb, The 24* bit in the formula will convert from a time (e.g. 12:30) to a number of hours (12.5). The formula assumes your drivers are not away across more than one midnight though. Hope this helps!

Hi, Adding to Hui's answer, this formula will work if you leave and arrive in the same day, and also if you arrive back after midnight, as in your example: =24*((B1&#60;=A1)+B1-A1) Where A1 is the departure time and B1 is the arrival time. this will return the result in decimal hours (e.g...