+ Reply to Thread
Results 1 to 32 of 32

Time duration calculation

  1. #1
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,633

    Re: Time duration calculation

    In C3 the drag down:

    Please Login or Register  to view this content.
    Quang PT

  3. #3
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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!

  4. #4
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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"?

  5. #5
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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!
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Time duration calculation

    do you mean for C18 you would like to see 00:10 instead of "NO MATCH"?

    you might need elaborate your criteria, is it to count the time for the same record ID as long between BEGIN_xxx and END_xxx?
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  7. #7
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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

  8. #8
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Time duration calculation

    Try this
    In C14 then drag down

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  9. #9
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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.

  10. #10
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Time duration calculation

    try C14=IF(LEFT(D14,3)="END",B14-INDEX(B13:B$14,MATCH(A14&"BEGIN",INDEX(A13:A$14&LEFT(D13:D$14,5),0),0)),"") and copy down..

  11. #11
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,633

    Re: Time duration calculation

    How about this in C14:
    Please Login or Register  to view this content.

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    Here's another.

    Array enter this in C14 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    By the way, this returns a 4:00 at END_JOB_FAILURE (C33) not included in sample output. Have I missed a detail?
    Dave

  14. #14
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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.

  15. #15
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    I am having difficulty visualizing this.

    May we have another upload that represents these situations?

  16. #16
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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!

  17. #17
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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!

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    In the first upload the example is clear and expected outputs also.

    With this new question can you do the same? I am having to imagine (guess) too much of the particulars. I usually guess wrong.

    Edit: Have you tried the formula on an example you think it wouldn't work with? As near as I understand it should work. The record IDs are accounted for in that formula.
    Last edited by FlameRetired; 12-03-2017 at 06:01 PM. Reason: Additional questions.

  19. #19
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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.

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    * Source type associations defined specifically, not by wildcard.
    Is it your understanding that the wildcard in my formula defines source type associations? It does not. The only purpose of COUNTIF(D14,"END_*") is to determine if the rest of the formula should execute or not. It has nothing to do with source type associations.

    Those association types are determined by rebuilding the source type (common suffixes ... by SUBSTITUTE) in the current row so as to leave nothing but Record IDs associated with BEGIN_ < Appropriate suffix > in the resulting array. All other matches are made on those filtered results.

    In the example above it appears there can be more than one instance of END with the same "matching" BEGIN preceding ... all with the same Record ID.

    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

    Which do you want paired and what is the logic to follow in the selection? What do you want to do with the other? Why is the elapse time for the 2nd END_SYSTEM_DEGRADED 00:15?

    Again, an additional upload spelling all this out should only have to include the range A14:E50.
    Last edited by FlameRetired; 12-03-2017 at 07:51 PM.

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    This is just a guess. Array enter this in C14. It returns only one time in C18. The rest are blanks or NO MATCH.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Somehow I don't think this is what you want.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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?
    Attached Files Attached Files

  23. #23
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    Edited: Shortened the formula

    Try array entering this in C14 and filling down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. With the exceptions of C23 ("NO MATCH" instead of blank) and C30 (0:05 instead of 0:01) this returns the other values.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Is there any chance those were 'typos'?
    Last edited by FlameRetired; 12-10-2017 at 09:02 PM. Reason: shortened formula

  24. #24
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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?

  25. #25
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    Try array entering this formula. It just has IFERROR wrapped around the formula.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    Perfect!

    Thank you VERY much!

  27. #27
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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
    Attached Files Attached Files

  28. #28
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Time duration calculation

    it says : negative time so you see #######(#)
    check all dependecies

    these values are ok?

    datetime.jpg
    Last edited by sandy666; 01-05-2018 at 12:34 PM.

  29. #29
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    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.

  30. #30
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    Your initial formula in row 14 ($B$14:$B104) was including times greater than the current time. I replaced all the $B$14:B104 range references to $B$14:B14 . I did likewise for columns A, D and E.

    Array entered in C14 and filled down this returns no negative "times". I did not do a pains taking check, but it appears to do what you want.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 01-05-2018 at 04:58 PM.

  31. #31
    Registered User
    Join Date
    11-21-2017
    Location
    Phoenix, Arizona
    MS-Off Ver
    2010
    Posts
    17

    Re: Time duration calculation

    I noticed that as well. I have this sheet going out and and making an external connection to an mssql db and pulling in the data. For some reason, the formula in C14, D14, E14 and so on, the cell references in the formula get appended in an odd way. It appears to add the last row number that has data in it to the syntax in C14 and than continues to increase for each subsequent cell reference in column C. You can see what I mean by selecting the cells in column C.

    Keep in mind, the C14 cell starts with the following formula prior to making its connection for the external data but it modified with the odd row number append detailed above:

    =IFERROR(IF(COUNTIFS($A$14:A14,A14,$E$14:E14,E14,$D$14:D14,"BEGIN*")-COUNTIFS($A$14:A14,A14,$E$14:E14,E14,$D$14:D14,"END*")>1,"NO MATCH",
    IF(LEFT(D14,3)="END",B14-INDEX($B$14:B14,SMALL(IF((LEFT(D14,3)="END")*(LEFT($D$14:D14,5)="BEGIN")*($A$14:A14=A14)*($E$14:E14=E14),
    ROW($A$14:A14)-MIN(ROW($A$14:A14))+1),COUNTIFS($A$14:A14,A14,$E$14:E14,E14,$D$14:D14,"END*"))),"")),"NO MATCH")

    If I manually obtain the data and than drag the formula in column C down, it does not have this issue.
    Last edited by excel4jms; 01-13-2018 at 06:55 PM.

  32. #32
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2412
    Posts
    13,615

    Re: Time duration calculation

    ... I have this sheet going out and and making an external connection to an mssql db and pulling in the data. For some reason, the formula in C14, D14, E14 and so on, the cell references in the formula get appended in an odd way. It appears to add the last row number that has data in it to the syntax in C14 and than continues to increase for each subsequent cell reference in column C. ...
    I am not familiar with external connections or sql. I don't even know what questions to ask.

    I'll see if I can get us some help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculation of Time duration in hours from 24hours input data
    By maxonline in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-04-2016, 10:51 AM
  2. Problem with calculation in a time duration formula
    By hal87 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2015, 09:36 PM
  3. [SOLVED] need help with duration (cumulative time) calculation
    By hgeorges in forum Excel General
    Replies: 4
    Last Post: 07-29-2014, 04:01 PM
  4. [SOLVED] Sumproduct with Time Duration calculation
    By gav0101 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2012, 06:34 PM
  5. Using duration in a calculation H:mm:ss to decimal
    By grigsy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-27-2011, 03:33 AM
  6. Having problem with Duration calculation
    By Dreammy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-23-2010, 01:44 PM
  7. Having problems with a duration calculation
    By Tommicpet in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2008, 10:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1