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...
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<=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...
Great, thanks Colin, I'll give that a go!
The software opens the excel report when I export it, so it's sat there looking ready to use but sadly more inaccessible than I was expecting it to be...
Thanks Colin,
I had a sinking feeling that may be the case, is there a way to get at them in VB at all or would I need to re-open them from the file directory into the right excel instance?
Hi,
Code is as follows, I've not tested if the middle bit works as it's not picking up any workbooks to test with and my VB syntax isn't that great without debugging! My workbook with the vbcode has 7 worksheets, one for each day I want to import data to A1:I100.
dim wb as workbook
For...
Hi, Hope someone can help, I have a program which can output daily reports to excel format, and I'm trying to automatically collate a weekly view from the 7 open files. I was hoping to use:
For Each wb in Workbooks
...do the collating stuff...
Next wb
But the excel workbooks opened by the...
Thanks for the help so far Narayan,
Sadly I can't upload stuff due to IT restrictions on the network I'm posting from... The sheet is just a longer list of similar strings which were written over many years without a naming convention as you can probably tell and I'm trying to sort then into...
Hi,
A quick check through my excel settings and I've switched on multithreaded calculations (no idea why this wasnt set up) and restarted excel after saving as .xlsm, the formula's now working I think as written...
but for e.g. 37.5BLANK it's returning just the 37.5 but I'd like to return...
Hi,
A small smaple of the data - I'm looking to return the same list but remove the last text characters after the last number in the string so e.g. M-F9 and M-F17 etc. fro the list:
TRASU-TH13
NCFLX4
M-F9B
37.5BLANK
4WKUDD7
M-F17F
TRAMFD14
M-F14A
FM-TH12EE
M-F17A