Is there any way (I can't find it anywhere) to calculate the time difference (in minutes) when I have this format of time in a single cell:
Example:
| A (start time-end time / start time-end time) | B (sum of time difference in minutes) |
|---|---|
| 00:00-06:00 / 22:00-24:00 | 480 |
CodePudding user response：
Use FILTERXML to return an array of the times and subtract the 1 and 3 position from the 2 and 4 position respectively.
Wrap in IFERROR to deal with there not being a second time inputted.
Then sum:
=SUM(IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/","-"),"-","</s><s>")&"</s></t>","//s["&{2,4}&"]")-FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"/","-"),"-","</s><s>")&"</s></t>","//s["&{1,3}&"]"),0))*24*60
Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode
Note: FILTERXML is only available on later versions of Excel and not available on Mac versions.
CodePudding user response：
Thanks! But I found that this works fine for me:
=IFERROR(IF((LEN(C5)=25);SUM(TEXT((MID(C5;7;5))-(LEFT(C5;5));"[mm]");TEXT((RIGHT(C5;5))-(MID(C5;15;5));"[mm]"));SUM(TEXT((MID(C5;7;5))-(LEFT(C5;5));"[mm]")));"")