0

I am trying to calculate (using worksheet formula based solution only) individual time-durations and total time duration from the slash '/' separated time-texts in two cells (each value of first cell are time-from and corresponding values in the second cell are time-to) in Excel (still using Excel 2007 only)

Input cells (B1 contains times-from data, however B2 contains corresponding times-to data)

Cell-B1:  "00:20 / 00:58 / 09:25 / 12:05 / 17:26 / 21:58"
Cell-B2:  "00:45 / 07:57 / 11:41 / 15:57 / 21:27 / 23:48"

Expected Output (B6 to show individual time duration, and cell B7 to show grand total of all durations)

Cell-B6:  "00:25 / 06:59 / 02:16 / 03:52 / 04:01 / 01:50"
Cell-B7:  19:23:00

The data organized and expected output results are as shown in below snapshot image.

SNAPSHOT IMG: View my input data cells, and expected output data cells

I understand, VBA can do it easily and I do have basic VBA knowledge, however is there a possible single formula (may or may not be array formula) based solution to show individual time-duration and sum of the all the calculated time-duration? I simply want to avoid VBA Macros and also do not want manual process of Text To Column (using delimiter '/') then do individual time-duration calculation, and then finally show all the duration in joined form, and show the grand total of all individual time-durations.

To arrive the results, only few basic worksheet functions may only be used such as MID, FIND, SUM, SEARCH, TRIM, SUBSTITUTE, INDEX, ROW etc.

Improve this question
5
  • Hi and welcome to Superuser. Please note that this is not a free code writing service. That said, if you could show us what you have tried so far and explain the problem with it, we can take a look and help out
    – cybernetic.nomad
    Commented Apr 25, 2023 at 20:11
  • Hi, I have googled to find answer using of similar question, I at a time find a formula =SUM(VALUE(TRIM(MID(SUBSTITUTE(A1,",", REPT(" ", LEN(A1))), (ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*LEN(A1)+1, LEN(A1))))) which sums up comma-separated values in a single-cell value Example: in a single-cell comma-separated value is entered as: 26, 13, 7, 22, 25 and using above formula it sums up the values. Hence, I think there must be a similar solution for this too.
    – the Explorer
    Commented Apr 25, 2023 at 20:35
  • Have you tried to modify the formula to suit your needs?
    – cybernetic.nomad
    Commented Apr 25, 2023 at 20:46
  • Hi, I am able to access individual values entered in the slash '/' separated values in two cells, but do not know how to do mathematical operation (like finding differences, one by one) and sum up all answers. Values-in-1st-Cell Array-Formula = VALUE(TRIM(MID(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),(ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1))-1)*LEN(B1)+1,LEN(B1)))) Values-in-2nd-Cell: Array-Formula = VALUE(TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),(ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))+1))-1)*LEN(B2)+1,LEN(B2))))
    – the Explorer
    Commented Apr 25, 2023 at 20:53
  • Yes I have tried and modified to get results. Here is the so called discovered and modified worksheet formula, it gives the nearly result that I want but cant understand how. Could anyone please explain functionality of it, and correct the formula: =CONCATENATE(TEXT(VALUE(TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),(ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))+1))-1)*LEN(B2)+1,LEN(B2))))-VALUE(TRIM(MID(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),(ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1))-1)*LEN(B1)+1,LEN(B1)))),"[hh]:mm:ss")&" / ")
    – the Explorer
    Commented Apr 25, 2023 at 22:23

2 Answers 2

Reset to default
1

Adapting your formula to SUM differences

=TEXT(SUM(
   VALUE(TRIM(MID(SUBSTITUTE(B2,"/",REPT(" ",LEN(B2))),
      (ROW(INDIRECT("1:"&LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))+1))-1)*
      LEN(B2)+1,LEN(B2))))-
   VALUE(TRIM(MID(SUBSTITUTE(B1,"/",REPT(" ",LEN(B1))),
      (ROW(INDIRECT("1:"&LEN(B1)-LEN(SUBSTITUTE(B1,"/",""))+1))-1)*
      LEN(B1)+1,LEN(B1))))), "hh:mm:ss")

Improve this answer
1

Formula

Sorry, saw the requirements to exclude any of the functionality that makes this easy ;-) Maybe leave this up for someone using a current version of Excel that stumbles on it.

=LET(
   durations, MAP(TEXTSPLIT(B1," / "),TEXTSPLIT(B2," / "),
      LAMBDA(from,to,TIMEVALUE(to)-TIMEVALUE(from))),
   VSTACK(
      TEXTJOIN(" / ",FALSE,BYCOL(durations,LAMBDA(dur,TEXT(dur,"hh:mm")))),
      TEXT(SUM(durations), "hh:mm")))
  1. The LET function is used to enable naming/storing intermediate calculations for later use
  2. TEXTSPLIT "from" and "to" strings on " \ " and MAP values into the LAMBDA function's from and to variables
  3. LAMBDA's formula uses TIMEVALUE to convert the mapped strings and returns the difference of each mapped pair as a column in a one-row array named durations
  4. VSTACK is used to return a 2-row (1-column) array:
    • Row 1:  TEXJOIN concatenates durations' values delimited by " / " after first applying the TEXT format "hh:mm" via another LAMBDA function
    • Row 2:  TEXT format "hh:mm" is applied to the SUM of durations' values.

Improve this answer
3
  • 1
    Above answer is also correct, however it may not work in Excel 2007 and resulting with #Name error
    – the Explorer
    Commented Apr 26, 2023 at 20:03
  • Yes, sorry. I belatedly recognized that and added a note to the answer. There isn't much in it that will not break in Excel 2007. Not much use to you but left it up for future searchers who have more recent versions.
    – Blindspots
    Commented Apr 26, 2023 at 21:29
  • In trying to figure out the options, I found a link to this spreadsheet Excel Functions: Compatibility Reference (2010, 2007 & 2003 in this 2012 Stack Exchange answer by Ellesa
    – Blindspots
    Commented Apr 26, 2023 at 21:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .