Calculate error times based on recurring text string

PSD

New Member
Joined
Sep 10, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am looking for an efficient way to calculate the error time from a sheet of data based on the name column (B).
When the value of a certain error is 1, the error is started and when the value is 0, the error is solved (column C).
There are a lot of different errors, which sometimes simultaneously are present (An earlier error is not solved yet, but the next error is already present).
Being inexperienced with Excel, this makes it difficult for me to extract the error times (time stamps in column D) without having to filter X amount of error names in order to successfully calculate the error times.

My question is, does anyone have a shout on an efficient line of code or macro which best handles what I wish to achieve?
(Office 365)
 

Attachments

  • MrExcelexample.png
    MrExcelexample.png
    80.5 KB · Views: 25

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to the Forum!

Do you mean something like this?

ABCDEF
1Elapsed
2Error Name 1114 Sep 202110:20 AM5:40
3Error Name 2114 Sep 202111:30 AM20:30
4Error Name 1014 Sep 20214:00 PM 
5Error Name 3115 Sep 202112:30 AM6:40
6Error Name 1115 Sep 20214:00 AM2:45
7Error Name 1015 Sep 20216:45 AM 
8Error Name 3015 Sep 20217:10 AM 
9Error Name 2015 Sep 20218:00 AM 
10Error Name 4115 Sep 20219:00 AM1:30
11Error Name 1115 Sep 202110:25 AMOpen
12Error Name 4015 Sep 202110:30 AM
Sheet2
Cell Formulas
RangeFormula
F2:F11F2=IF(B2=0,"",IFERROR(SUM(INDEX(FILTER(C3:D$12,(A3:A$12=A2)*(B3:B$12=0)),1,))-SUM(C2:D2),"Open"))
 
Upvote 0
Solution
Hi Stephen,

Indeed this was what I meant, and your proposed solution worked!
Thank you for the help.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,847
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top