To Calculate Number of Days

[cyliyu]

I was given a task to go thru a list of few thousand records to calculate the number of days the same serial number recurrences.

For e.g. The serial number 10148 in the sample sheet occurred 5 times, I need to calculate the number of days based on the last 2 date (22-Jun-17 to 24-Aug-17=63days) and ignore the first 3 dates.

Condition:
1) if the serial number occurred only one day, no calculation is required. maybe display N.A
2) if the serial number occurred more than 2 times, calculate based on the last 2 dates, e.g. 2 times = the last date - last 2nd date, 3 times = the last date - last 2nd date and ignore the 1st date... and so on

Is it possible to automatic this process?
I'm alright if modify the spreadsheet is required in order to meet these conditions.

Need help, please.

 

[NARAYANK991]

Hi ,

See if this works.

Narayan

 

[cyliyu]

Thanks Narayan for your fast response.
1) Is possible to display the number of days in the last occur date? instead of the last 2nd date?

2) I have make some changes to your formula. hopefully this will help when I create a pivot table/chart.
=IF(I6 = 1, "NA", IF(COUNTIF(E6:E$40,E6) = 2, INDEX(D7:D$40, MATCH(E6, E7:E$40, 0)) - D6, "R"))

Once again, thank you for helping me.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[NARAYANK991]

Hi ,

If you wish to avoid the helper column , this formula will work :

=IF(I6 = 1, "NA", IF(COUNTIF(E6:E$200,E6) = 1, LARGE(IF($E$6:$E$200 = E6, $D$6:$D$200), 1) - LARGE(IF($E$6:$E$200 = E6, $D$6:$D$200), 2), "R"))

This is an array formula , to be entered using CTRL SHIFT ENTER.

Narayan

 

[cyliyu]

Thanks, Narayan.

The formula avoids the helper column work great.

It gives me a big help instead of manually go thru all the record. Thanks and appreciated.

 

[cyliyu]

By the way, is it possible to display a empty cell if the Recurrence cell is empty instead of "R"?
I tried to add in IF(I6="","",.... but it doesn't work.

 

[NARAYANK991]

By the way, is it possible to display a empty cell if the Recurrence cell is empty instead of "R"?
I tried to add in IF(I6="","",.... but it doesn't work.

Hi ,

The problem is the check =IF(I6 = "" ; since the formula in column I is a COUNTIF , in the absence of a count , it will return 0.

Change your check to =IF(I6 = 0 ...

Narayan

 

[cyliyu]

If works. great thanks.

 

[cyliyu]

I came across 2 serial number "00951" and "951" today, but the formula treated both are the same number but indeed they are not. How can I solve this?

 

[cyliyu]

Need help again to improve the compared results.

I tried to modify the formula to compare the date code if 2 serial number "match", with and without zero in front them to prevent result #NUM! happening, but no success.

Can anyone help?

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[cyliyu]

Hi, NARAYANK991,
Thanks. your new spreadsheet with the new formula considers both e.g. 118 and 00118 are the same number and compute the No of Days but indeed they are 2 different serial number with different product build date code.
Not sure is there a way to differential them?

 

[NARAYANK991]

Hi ,

Is this the way you wanted the results ?

Narayan

 

[cyliyu]

Hi ,

Is this the way you wanted the results ?

Narayan

Yes, The results is what I want.
I didn't notice can make changes to the "Recurrence" column and retain the "No of days" formula.
I may just have to ignore the empty serial number cell that created an error of "830".
Thanks again.

 

[NARAYANK991]

Hi ,

You can use an IF function :

=IF(E6 <> "", SUMPRODUCT(--($E$6:$E$870=$E6)), "")

Copy this down.

Narayan

 

[cyliyu]

Thanks,

Problem solved.
Appreciate your help.

 

[cyliyu]

Hi ,

You can use an IF function :

=IF(E6 <> "", SUMPRODUCT(--($E$6:$E$870=$E6)), "")

Copy this down.

Narayan

Hi, Narayan,

Sorry, I just discovered I have overlooked the 'date code' column J when same serial number occurred.
The 'reoccurrence' column will count the same serial number occurred 2 times, but in fact, it shouldn't because both are built in 2 different dates.
Not sure based on your help from the existing formula, is it possible to take this into consideration and further improve it?





Recurrence

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[cyliyu]

Hi ,

See the attached file.

Narayan

Thanks, Narayan.
I really appreciate your great help and fast response.