Long Calculating Time
Aug 26, 2008
I managed to put together an array formula to calculate the last date that a rep made a sale. It checks two other tabs in the workbook to find the date, and if none is found, it leaves the cell empty.
{=IF(MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A))=0,"",MAXA(IF('Daily Compliance'!A:A=B48,'Daily Compliance'!O:O),IF('Daily Compliance 11-07 to 4-08'!B:B=B48,'Daily Compliance 11-07 to 4-08'!A:A)))}
But it takes sometimes up to 4-5 minutes to make the calculations. Is there possibly a way to simplify it so that it calculates faster, with the same results?
View 3 Replies
ADVERTISEMENT
Aug 3, 2006
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"
Dim Cmnt
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Row > 3 Then
If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6
End If.....................................
View 3 Replies
View Related
Jul 18, 2009
I work with a lot of time based worksheets for a 24/7 operation and constantly use the formula:
=IF($E17>$D17,(HOUR($E17)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)),((HOUR($E17)+24)+(MINUTE($E17)/60))-(HOUR($D17)+(MINUTE($D17)/60)))
to calculate times worked where $D17 is start time and $E17 is finish time. Shifts can start and finish the same day ($E17>$D17) or go past midnight ($D17>$E17).
To make matters even more interesting, there are a number of variables that require adding either "and" and/or "or" conditions to this formula so I can end up with several of these nested and it gets pretty complicated.
I'd like to have a UDF so I can just enter the formula:
=CalcTime(Start Time,Finish Time)
View 9 Replies
View Related
Oct 4, 2013
I have a long date and time text value in a cell such as "2013/10/02 07:43:39.39", where the fractions of a second are very important.
I can use cdate([text]) to convert the text string to a value, but only if i omit the seconds fraction: "2013/10/02 07:43:39".
Otherwise i get a type mismatch error.
Is there any way i can get the whole date/time string converted to a date value? The only way i can think to do it it at the moment is to convert the date/time, then divide the seconds fraction by 86400 and add the 2 values. Is there a better way?
Secondly, i have a large number of these date strings, typically >30k lines x 7 columns. Each string is preceded and trailed by a [space] character.
I can strip these extra spaces no problem. And, i can "loop" to perform it, again no problem. But it's going to take time to perform and i'd rather not if i can avoid it.
View 2 Replies
View Related
May 28, 2009
I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.
The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.
This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.
View 4 Replies
View Related
Mar 10, 2004
Any code I can stick into the end (plus maybe start) of a macro to display the length of time it took for the macro to run?
View 9 Replies
View Related
Jun 11, 2014
I am having a little trouble with a spreadsheet I am creating, the formulas and cell formatting I should be using to enable this to work.
Here goes:
in cell E3 I have a time started (e.g 12:45 pm), In F3 i have time finished (e.g 2:30 pm)
So, what i'm wanting is the time taken in G3 and also, i have the amount of units that is entered manually in H3. I would like I3 to show the time taken per unit.
View 2 Replies
View Related
Feb 20, 2009
I'm battling to come up with the formula to calculate time and a half and double time. My boss wants me to show the overtime worked AFTER 40 hours has been worked. I have attached a copy of my spreadsheet.
View 4 Replies
View Related
Jun 23, 2009
I know the title is a bit vague, but I cant think how else to word it!
I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target.
I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
View 9 Replies
View Related
Jan 11, 2010
I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:
We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.
View 3 Replies
View Related
Jan 20, 2014
[URL]
Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.
I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).
But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.
- I would say I have about 2000 rows, and 15 columns.
- They have sumifs formulas.
- They link to a different workbook.
- The workbook I am working on saves to the network
- the source of my sumifs are also in the same folder on the network
- the recalculation takes about 10 seconds at most
- i have turned off recalculate before saving, it is all on manual calcs
- when i hit save, there are no calcs being performed
- there are no macros in the workbook
- there are only about 2 names in the name manager
- then it freezes for about 10 minutes.
- then the progress bar starts moving then it saves.
What is it doing in those 10 minutes?
1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.
Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?
View 4 Replies
View Related
May 8, 2009
I have cells of data with a long string of numbers such as: 20090507225836. Is there any easy way to convert them to date/time format, such as below? 5/7/2009 22:58:36
View 5 Replies
View Related
Feb 12, 2010
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
View 6 Replies
View Related
Mar 15, 2012
I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell
{=INDEX(List,MATCH(0,COUNTIF($b$1:b1,List),0))}
View 5 Replies
View Related
Mar 17, 2007
I have the VB Editor open and am manually activating different workbooks in Excel (with 20/25 modules each), the VB Editor goes through a process of maximising each and every module in the workbook I have selected before I can edit any code or, indeed, do anything in the workbook. This process takes about 8-10 seconds every time I select a different workbook in excel! I have played around with the settings "Full Module View" in the options section of the VB editor, but to no effect.
View 3 Replies
View Related
Mar 16, 2009
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
View 4 Replies
View Related
May 19, 2008
I have a large Excel 2007 file, around 60.000KB. 54.000KB are due to one of the worksheets where I have 8760rows x 160columns with data. The calculation time is not a problem, it is very fast, it only takes 2/3 seconds. The problem is when I open or save the file, it takes around 2 minutes... it is not too much, but it becomes too long when one has to open and save it several times. It there any trick to decrease the time when openning or saving an excel file??
View 3 Replies
View Related
Apr 18, 2012
I have a large file that takes a long time to close.
I would like to display a message that says " SAVING - PLEASE WAIT".
But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.
How can I do this?
View 7 Replies
View Related
Nov 12, 2009
Firstly, i'd like cell G5 to show the amount of time worked i.e diff between E6 & F6. Secondly I'd like help with the formula for cells AF6 & AG6 which would require AB6-AC6 divided by the frames, every time I try it I get a messed up answer.
View 14 Replies
View Related
Sep 9, 2009
=INDEX({"$5.00","$3.00","You Were Late"},MATCH(L17,{7:00am,7:05am,7:06am},1))
i have this formula in excel...this formula sanrv1f posted to help with another question (values changed) but i thought i would work with what i wanted but i get a (Value) error
so what im trying to do is base on the system time is if the person typed in
if they were early 6:45am to 7am they get Extra $5 on top of the $5 they get for being on time.
6:55am gets $5
7:00am they get $5.00 for being on time
if they type 7:01am to 7:05am they get $3.00
if they type 7:06 to 7:10am would return You Were Late
after 7:10am would return No Pay
so Ex
D4 *answers in E4 *
7am would get $5
7:03am am would get $3
7:07am would get You Were Late
7:11am Would get No Pay
if they were on time 5 times (ie 5days in a row) they would get a bonus of $20 i tried factoring this in could not do it :/
View 9 Replies
View Related
Feb 21, 2010
basically I need an excel sheet to calculate the period of time between cells.
For instance, in A1 I write:
18-Feb-10
In A2 I write:
13:00
Unfortunately I cannot write 18-Feb-09 13:00 in just one cell because it doesn't work.
Now, in B1 I write 19-Feb-10
and in B2 I write 15:14
Now we know that 26 hours and 14 minutes have elapsed between these two times. So if I want this to automatically be calculated in B3, what do I do?
Also, how do I apply the formula to all cells in column 3 so all I have to type in is the date (in column 1) and the time (in column 2)?
View 9 Replies
View Related
Mar 30, 2014
I'm having a DICKENS of a time on this one. To explain, I have 3 columns where time is entered: a 'Time of Call', 'Time of Arrival', and 'Response Time'. The 'Response Time' cell autcalculates by simply subtracting the 'Time of Arrival' form the 'Time of Call' and using the difference to show the response time. To make time entry into the 'Time of Call' and 'Time of Arrival' simplier for my co-workers, I formatted those cells as 00:00 so that anytime you enter a number it will convert it to a time format (i.e., if you enter in the numbers 745, it will show as 7:45 in the cell). Because of this, when the response time is configured, it does it in hundreds instead of time format. So, if you enter in 7:45 as your time of call and 8:15 as your time of arrival, it shows 70 minutes as a response time because it sees the 7:45 as a whole number...745. Therefore, 815-745 DOES equal 70, but...that's not what I'm looking for. I want it too configure this as time...not as whole numbers.
View 2 Replies
View Related
Apr 2, 2014
I know how to calculate the duration elapsed between start and finish time. But how can I display the total time if for eg it's ends up being 300hrs?
I need the hours spent in the building per day and then the grand total per month.
View 1 Replies
View Related
Jan 26, 2009
I'm trying to calculate the variance between planned date & time of arrival vs actual date & time of arrival.
I attach the workbook as am a bit useless at explaining myself....
What I've done is in H14 subtract the actual date of arrival (F14) from planned date of arrival (C14). This result is the only way I could think of dealing with crossing over midnight. As a result I14 should subtract the actual time of arrival (E14) from planned time of arrival (B14):
=SUM(E14-B14,H14)
This method works well when the arrival was later than expected but doesn't work if the arrival was sooner than expected.
View 6 Replies
View Related
Mar 11, 2009
I've browsed the net searching for a solution to my problem and found one solution altough not entirely what i was after, but it was very close. It was provided on another forum, of which i'm not a member.
[url] ...
I have a TAT target of <= 2hours, however this is complicated by weekends, holidays and jobs received after working hours and on weekends. I've attached the file, which contains detailed information about my problem.
View 2 Replies
View Related
Aug 17, 2013
I'm calculating the difference in time:
9:54-9:45 = 0:09
Now to convert this in minutes (in number format), I multiply this number with 1440:
0:09*1440 = 9
When I click the cell this number is actually 9.0000005, rather than exact 9. I need it to be exact 9 since I have to match this number from other excel sheet.
To sum up:
This is what I expect:
31.99999 should be changed to 32
10.000001 should be changed to 10.
View 3 Replies
View Related
Jun 2, 2009
I am trying to track how long a macro takes to run from start to end.
View 4 Replies
View Related
May 15, 2012
I need to calculate an SLA.
I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:
dd/mm/yy hh:00
I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.
I need a macro that compares the two and works out the following:
< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"
View 4 Replies
View Related
Mar 18, 2007
I have a sheet to calculate Time Elapsed based on StartDT & EndDt taking into consideration the actual working hours in any working day and the function exclude the weekend. I am not a guru in excel but i found this formula in this website .
=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0)))),0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))
the formula works perfectly except for the fact that in our part of the world we have "Friday & Saturday" as the weekend days instead of Saturday and Sunday.
is there a way to get this formula or any similar formula to take different weekend days and do exactly what i need?
View 9 Replies
View Related
Sep 15, 2007
I have two questions about a spreadsheet where I am trying to calculate how much flexi time has been accured.
So far I have managed to do this: ....
View 9 Replies
View Related
