ı couldn't figure out how to make this

[korayuyanık]

Hi all. ı have two charts .. first chart has 2 columns : date /worker id number. (date column represents date of occupational accident)

for example ;

25/01/2023 5136785
10/04/2023 5139188
25/05/2023 5139188
19/09/2023 5127902
22/11/2023 5127902
22/11/2023 5139188

and in the second chart ı have 2 columns : dates / worker id number. (date column represents the dates that workers who cannot work due to accident and have medical report)

for example;

26/01/2023 5136785
27//01/2023 5136785
11/04/2023 5139188
12/04/2023 5139188
13/04/2023 5139188
25/05/2023 5139188
26/05/2023 5139188
23/11/2023 5139188
24/11/2023 5139188
25/11/2023 5139188
26/11/2023 5139188
27/11/2023 5139188
20/09/2023 5127902
21/09/2023 5127902
25/11/2023 5127902
26/11/2023 5127902
27/11/2023 5127902

now my problem is that ı need a third column in the first chart consist of how many days they couldnt work due to accident

for example;

25/01/2023 5136785 2
10/04/2023 5139188 3
25/05/2023 5139188 2
19/09/2023 5127902 2
22/11/2023 5127902 3
22/11/2023 5139188 5

one trick you should realise that the day when they have accident and the day when their rest starts are not the always same. their rest start with medical report and they take the report in sameday or in 1-3 days after accident.
and other trick is that in their resting period sometime a gap between dates . in other words dates are not always successive. but resting days belong to first accident is always before the date of second accident in reason.

thank you

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[shina67]

To calculate the number of days a worker couldn't work due to an accident, you'll need to match the accident dates from the first chart with the medical report dates from the second chart for each worker ID. Then, you can calculate the number of days between the accident date and the first medical report date, considering any gaps in the resting period.

Here's how you can do it:

Add a Third Column in the First Chart:

In the third column of the first chart, you'll calculate the number of days the worker couldn't work due to an accident.

Assuming your data is in columns A, B, and C (Date, Worker ID, Number of Days) in the first chart, and columns E, F (Date, Worker ID) in the second chart, and your data starts from row 2 (with headers in row 1):

In cell C2 of the first chart, enter the following formula:


=IFERROR(IF(B2<>B1, "", IFERROR(MATCH(A2, $E$2:$E$100, 0)-1, "")), "")

This formula checks if the worker ID in the current row is different from the one in the previous row. If it is, it means it's a new worker, so it leaves the cell blank. Otherwise, it searches for the accident date in the second chart (medical report dates) and calculates the number of days between the accident date and the first medical report date.

Drag this formula down for all rows in the third column.

Adjust Range:

Ensure that the range $E$2:$E$100 in the formula covers all the medical report dates in the second chart.

Handle Gaps in Resting Periods:

If there are gaps in the resting periods, you may need to modify the formula to handle this. One approach is to calculate the difference between the current accident date and the next accident date for the same worker ID.

Format the Result:

Format the third column as a number to display the number of days.

This formula should give you the number of days each worker couldn't work due to an accident.
Make sure to adjust the formula and range references based on your actual data range and layout.