LutonBarry
11-11-2015, 12:35 PM
Well I've tried and failed but I wonder if any of you on the forum can provide a formula preferably or failing that some VBA code thatcan solve this problem I have.
On the attached spreadsheet I need to calculate elapsed times between certain activities. They are in Column C and are labelled HARDWARE-XX-02 and HARDWARE-XX-04. So it is a calculation of 04 minus 02. We have similar for other calls which are HARDWARE-UK-03 and HARDWARE-UK-05, so 05 minus 03. These activities may or may not exist or perhaps exist in multiples within the same ticket, the ticket ref is in Col A.
The principles of the start of the time measure are:
The date/time in Col E of the earliest if multiples exist of HARDWARE-XX-01 or HARDWARE-UK-02.
If those activities do not exist the Date/Time in Col D of the first line of the Ticket ID (Col A).
The principles of the Closing time are:
The date/time in Col E of the latest if multiples exist of HARDWARE-XX-04 or HARDWARE-UK-05.
If those activities do not exist the Date/Time in Col E of the last line of the Ticket ID (Col A).
I have the following formula in Col G "=IF(AND(A6=A3)*OR(C6="HARDWARE-XX-04",C6="HARDWARE-UK-05"),(NETWORKDAYS(E3,E6,Holiday)-1)*(WorkDayFinish-WorkDayStart)+MOD(E6,1)-MOD(E3,1))" that works if the ticket flow is adhered to, but it doesn't in all cases hence this request.
I attach a sample spreadsheet and have highlighted changes in Ticket ID.
So for Ticket 1 the formula works OK as the call flow is as it should be.
Ticket 2 the criteria exist but have other activities in between.
Ticket 3 no criteria at all.
Ticket 4 multiple HARDWARE-XX-04 activities
Ticket 5 No HARDWARE-XX-02 activity.
Etc.
I will be in raptures of appreciation if someone can crack it, but if anyone can it is the great folks on this forum.
On the attached spreadsheet I need to calculate elapsed times between certain activities. They are in Column C and are labelled HARDWARE-XX-02 and HARDWARE-XX-04. So it is a calculation of 04 minus 02. We have similar for other calls which are HARDWARE-UK-03 and HARDWARE-UK-05, so 05 minus 03. These activities may or may not exist or perhaps exist in multiples within the same ticket, the ticket ref is in Col A.
The principles of the start of the time measure are:
The date/time in Col E of the earliest if multiples exist of HARDWARE-XX-01 or HARDWARE-UK-02.
If those activities do not exist the Date/Time in Col D of the first line of the Ticket ID (Col A).
The principles of the Closing time are:
The date/time in Col E of the latest if multiples exist of HARDWARE-XX-04 or HARDWARE-UK-05.
If those activities do not exist the Date/Time in Col E of the last line of the Ticket ID (Col A).
I have the following formula in Col G "=IF(AND(A6=A3)*OR(C6="HARDWARE-XX-04",C6="HARDWARE-UK-05"),(NETWORKDAYS(E3,E6,Holiday)-1)*(WorkDayFinish-WorkDayStart)+MOD(E6,1)-MOD(E3,1))" that works if the ticket flow is adhered to, but it doesn't in all cases hence this request.
I attach a sample spreadsheet and have highlighted changes in Ticket ID.
So for Ticket 1 the formula works OK as the call flow is as it should be.
Ticket 2 the criteria exist but have other activities in between.
Ticket 3 no criteria at all.
Ticket 4 multiple HARDWARE-XX-04 activities
Ticket 5 No HARDWARE-XX-02 activity.
Etc.
I will be in raptures of appreciation if someone can crack it, but if anyone can it is the great folks on this forum.