PDA

View Full Version : Formula Conumdrum Is Excel up to it.



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.

SamT
11-11-2015, 03:08 PM
First sort the table by Ticket ID, then Activity ID then Starttime

LutonBarry
11-11-2015, 04:12 PM
First sort the table by Ticket ID, then Activity ID then Starttime

But Sam my formula doesn't accomodate the variables in the possible scenarios that could be presented even if I did dort the table in the fashion you have proposed.

Sometimes the 'HARDWARE' activities are present and sometimes they aren't etc.