Time duration calculation

Any help on this would be MUCH appreciated!

What I am trying to accomplish is basically, determining the time duration between a start and end time value relevant to a given Record id and Source entry.

The formula would need to identify matching Record ID's, then determine if the Record ID is associated with a BEGIN or END Source so that the Duration between the two can be calculated.

There would also be circumstances when the BEGIN time would not be on the same sheet as the END time. It would be ideal if those particular entries could be flagged with something like "NO MATCH". This would tell the user that they would need to look at a previous sheet to get the BEGIN time info needed.

I have attached an example sheet to hopefully help with the explanation.

Thanks.

bebo021999,

Thank you for your response and getting me going in a better direction with this. I wanted to see if we could expand slightly on the formula with the BEGIN and END criteria. Essentially, there are instances in my sheet where I have multiple BEGIN and END types, as an example BEGIN_SYSTEM_DEGRADE with the END_SYSTEM_DEGRADE or BEGIN_SERVICE_DEGRADE with the END_SERVICE_DEGRADE and so on. I am not sure if there is a way to do this with a wildcard or a nested IF statement for handling multiple BEGIN and END types? Thanks again!

I am finding that when the BEGIN doesnt exist, the END cell duration shows #N/A which causes issues for summing all total END times. Is there a way to maybe add a text output in that respective cell when the circumstance occurs like "NO MATCH"?

PLEASE HELP!

I am certain that this is not the most ideal way to accomplish the given task but I am using the following formula to "mostly" accomplish the time duration calculation:

Please Login or Register  to view this content.

Unfortunately, I am now running into issues with the "BEGIN_EMERGENCY_RFC" using the same "END" criteria as something else i.e. END_SYSTEM_DEGRADED, END_SERVICE_DEGRADED and END_SERVICE_DEGRADED.

I hope this makes sense.

ANY help with this is greatly appreciated!

Yes, it should have an associated time duration for the given "BEGIN*" and "END*" Source if the Record ID is the same. Unfortunately, The "BEGIN_EMERGENCY_RFC" Source entries are generating a no match for some reason when using the formula above. I also have "NO MATCH" being flagged by Conditional formating if the associated BEGIN/END Source match is not found on the sheet. This is to indicate to the user that the BEGIN/END Source may be on another sheet (previous day usually) The following are a list of the current "BEGIN" and "END" Sources that are needed.


BEGIN_SYSTEM_DEGRADED END_SYSTEM_DEGRADED
BEGIN_SERVICE_OUTAGE END_SERVICE_OUTAGE
BEGIN_SERVICE_DEGRADED END_SERVICE_DEGRADED
BEGIN_RFC_DEGRADED END_RFC_DEGRADED
BEGIN_RFC_OUTAGE END_RFC_OUTAGE
BEGIN_EMERGENCY_RFC END_SYSTEM_DEGRADED
BEGIN_EMERGENCY_RFC END_SERVICE_DEGRADED
BEGIN_EMERGENCY_RFC END_SERVICE_OUTAGE

Thank you for your response. In the example sheet you attached, cells C18, C31 and C40 should all have a time duration. All of these cells have a "BEGIN" Source reference of "BEGIN_EMERGENCY_RFC". For some reason however, since they reference an "END" Source referenced by another "BEGIN" Source, it is not calculating the duration.

It appears as though since it is sort of using a BEGIN and END wildcard, the correct duration is not being calculated correctly if one Record ID has multiple BEGIN and END sources. Is it possible to create a formula to allow specific BEGIN and END matches such as the following (NOTE there are duplicate "END" sources being used):

BEGIN_SYSTEM_DEGRADED END_SYSTEM_DEGRADED
BEGIN_SERVICE_OUTAGE END_SERVICE_OUTAGE
BEGIN_SERVICE_DEGRADED END_SERVICE_DEGRADED
BEGIN_RFC_DEGRADED END_RFC_DEGRADED
BEGIN_RFC_OUTAGE END_RFC_OUTAGE
BEGIN_EMERGENCY_RFC END_SYSTEM_DEGRADED
BEGIN_EMERGENCY_RFC END_SERVICE_DEGRADED
BEGIN_EMERGENCY_RFC END_SERVICE_OUTAGE

Please Login or Register  to view this content.

This formula does appear to correct the duration calculation issue but would it be possible to instead of using just a "BEGIN" and "END" wildcard but specify the actual BEGIN and END Source. This would allow for a NO MATCH to be triggered if either the BEGIN* or END* were not present on the sheet or if the associated BEGIN and END Source doesnt have an associated Matching Record ID. This would also account for a possible BEGIN and END Source that didnt actually have "BEGIN* or "END*" in the Source Name.

I really dont have anything I can upload that would be different from the time-duration-example-2.xlsx file above unfortunately.

The short explanation and over-all goal though is to be able to calculate the duration of time between two Source types (that can be customized and able to allow multiple "begin" Source types to reference the same "end" Source types) as long as they have matching Record ID's with an ability to distinguish between multiple BEGIN and END Source types per Record ID based on the respective time stamp.

I really hope that makes sense.

All the help is really appreciated!

I really dont have anything I can upload that would be different from the time-duration-example-2.xlsx file above unfortunately.

The short explanation and over-all goal though is to be able to calculate the duration of time between two Source types(that can be customized and able to allow multiple "begin" Source types to reference the same "end" Source types) as long as they have matching Record ID's with an ability to distinguish between multiple BEGIN and END Source types per Record ID based on the respective time stamp.

I hope that makes sense.

All the help is really appreciated!

The formula does work and produce the results expected for the most part. I just need to be able to have the NO MATCH displayed if the associated Source is not present or entered in error.

The output is still the same in this case, but I will try to maybe give an overview of some the elements of this sheet in hopes of making things a bit more clear.

Every row entry in the sheet is going to have the following:

Record ID: Number assigned to multiple row entries, defined by the Source.
Time: Time stamp given to each Source entry.
RT: Duration of time between Source entries based on Record ID and Source type association.
Source: Begin and end state/source of a given row entry
Service: Particular service type attached to a given Source entry.


RecordID could have duplicate entries for each Service type depending on the given Source entry, see "BEGIN" and "END" Source types below:

BEGIN_SYSTEM_DEGRADED - END_SYSTEM_DEGRADED
BEGIN_SERVICE_OUTAGE - END_SERVICE_OUTAGE
BEGIN_SERVICE_DEGRADED - END_SERVICE_DEGRADED
BEGIN_RFC_DEGRADED - END_RFC_DEGRADED
BEGIN_RFC_OUTAGE - END_RFC_OUTAGE
BEGIN_EMERGENCY_RFC - END_SYSTEM_DEGRADED
BEGIN_EMERGENCY_RFC - END_SERVICE_DEGRADED
BEGIN_EMERGENCY_RFC - END_SERVICE_OUTAGE
BEGIN_JOB_FAILURE - END_JOB_FAILURE

Each of the BEGIN and END Source types will have an associated Time stamp that is entered, sometimes multiple BEGIN and END Source types per Record ID. These are entered in a format typical to the following:

RecordID Time RT Source Service
-------------------------------------------------------------------
12345 01:00 BEGIN_SYSTEM_DEGRADED Corp Service 1
12345 01:30 00:30 END_SYSTEM_DEGRADED Corp Service 1
12345 02:00 BEGIN_EMERGENCY_RFC Corp Service 1
12345 02:15 00:15 END_SYSTEM_DEGRADED Corp Service 1
54321 02:20 BEGIN_RFC_OUTAGE Mgmt Service 1
54321 02:25 00:05 END_RFC_OUTAGE Mgmt Service 1

The BEGIN and END Source types should not vary from the list above and if it does, a "NO MATCH" is generated in the RT cell. A "NO MATCH" should also be generated if the respective BEGIN or END is not present in the sheet (Normally due to a BEGIN or END Source association being on a previous sheet or a incorrect BEGIN or END Source association being entered in error)

Key points:
* Accurate RT duration for multiple Source entries per RecordID.
* Source type associations defined specifically, not by wildcard.
* Ability to "mix and match" BEGIN and END Sources, as an example BEGIN_SYSTEM_DEGRADED is associated to END_SYSTEM_DEGRADED and BEGIN_EMERGENCY_RFC is associated to END_SYSTEM_DEGRADED.

I hope this helps a little to detail what I need to accomplish.

Thanks again.

I have attached another sheet that was modified to show an issue occurring with the formula not providing the correct duration time and or not displaying the "NO MATCH" in column C. Begin and End sources and their related Service are color coded to assist is visually showing the proper matches for this example.

Cells highlighted in red are showing the correct value which has been entered manually. When using the formula copied down from C14, the value that is displayed in incorrect.

It appears as though the formula appears to have inconsistencies when there are multiple begin and end state for a given RecordiD as well as being unable to identity when there is a Source value that does not have the corresponding Begin or End match.

Could there be a need to incorporate the actual Service into the formula in order to properly distinguish what thew correct duration and or if a NO MATCH parameter is met?

Hey FlameRetired. Just wanted to thank you again for helping with this, it is very appreciated!

I think you nailed the syntax! From my initial testing it does appear to be calculating the time duration properly, even when multiple begin and end sources are encounter.

One thing I am seeing though is that when a match for a given Source is not found, instead of displaying NO MATCH in the associated C Column cell, the #NUM! is being displayed?

Perfect!

Thank you VERY much!

I have encountered an odd issue with the time calculation that I need some help with.

For some reason, certain "BEGIN" and "END" match time calculations in column "C" are displaying the following error:

"Dates and times that are negative or too large display as #####"

The cell of course displays ###########

The odd thing is, if I change any of the adjacent cell data to some thing else that matches, the time calculation functions correctly.

For reference, the associated BEGIN/END row matches have been filled with the same cell color.

As an example:
Row 60 is the "BEGIN" and row 62 is the associated "END". If I change the Record id in column A for both of these to something other than what is already there and that matches, the formula in C62 functions properly.

I have attached an example sheet for reference

All other values are fine except the cells c62, c63, c75, c76, c79, c81, c82, c87 with ####### in them if I am understanding your question.