date difference (in days) with conditions

[bdav1216]

See attached:

When Column A has the same ID and Column D = "Final" OR "Interim", subtract Column E (date 1) from Column F (date 2) to output the difference in days in Column G.

- Column F (Date 2) could have different dates for the same ID in Column A. The calculation should use the most recent date in Column F (Date 2).
- If Column E (date 1)- Column F (date 2) results in a negative value, output a "N/A"
- If either date field Column E (date 1) OR Column F (date 2) is blank, output a "-"

[José Augusto]

Hi @bdav1216

Your post raises some doubts. Can you please clarify

Question 1: F - E or E - F?
Question 2: Does column E, for the same ID, have the same values?

Assuming Date2- Date1 and a positive answer for the second question, try the following formula)

Please Login or Register  to view this content.

[bdav1216]

Question 1: Yes, we can use Date 2 - Date 1 (that actually more sense than what I was saying).
Question 2: Column E will not always have the same date. In most cases they should. If they don't, is it possible to add logic (only when Column E dates are not the same) to output: "N/D"

Thank you. In your formula what the the "14,6") do? Would I need to make any adjustments on a larger dataset?

Also, is it possible to account for the "N/A" possibility (if the difference in dates results in a negative value)?

[bdav1216]

For the bottom example, ID: 1133451, the value from the calculation is 6. Can that logic be updated to output '0'? I want to use the earliest possible date so (1/1/2020) instead of (1/7/2020)

[José Augusto]

Hi

You can try this formula:

Please Login or Register  to view this content.

... In your formula what the the "14,6") do?

You can see that if you edit the formula and replace 14 by 14 (not mistake). You will see a list of aggregate function where 14 is greater and 15 is lower. Do the same for 6 used for ignoring some errors.

... Would I need to make any adjustments on a larger dataset?

The formula is quite time consuming, but you can increase the range for example to $E$2:$E$300, $F$2:$F$300, etc.
See the file for clarification

[bdav1216]

Thank you for the assistance! I did a review and a few modifications that I am hoping we can implement to wrap up with:

Data is attached with your current formula.

- I added one additional layer of logic: When both Column A AND Column B have the same value, proceed to the calculation. This will fix a scenario that I identified in my review.
- When there are blank dates for the same ID and job (Column A and B), can the formula be update to output a '-'.
As an example, ID: 1178736 is outputting a -44061. If all of the date values in Column F are the same and the result of the calculation is a negative value, can we update these scenario's to a default value of 'DD'?. There are other examples in the data where you will see the Column F with the same dates, but negative values from the date values in Column G.

To confirm my understanding of the default values
'-': This value is populated when there are blank date values for either date1 or date2. Is that correct?
'#N/A': This value is populated when there are different dates in Column F. Is that correct?

[JeteMc]

If I understand correctly then the following modification to José Augusto's formula eliminates the negative values in column I:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bdav1216]

Thanks JeteMc!

The other question in my last reply was how to also incorporate both Column A and Column B to have the same values in their respective columns.

Currently only Column A is being used, but I want to require that both A and B are used in the formula.

[bdav1216]

For the negative values (and all logic), the ID and JOB columns should resolve to the same value.

When you look at ID: 1155626 and Job: Job-A50328, there are now 3 different values ('-', 1 and 2) in the 3 rows. Since there is date data to calculate, but it results in a negative value, it would be fine to update to a default value like "-" or "DD".

For ID: 1178736 and JOB Job-A70235 there are 4 rows, but one of DATE fields (H) has a blank. When there is blank data data, output a default '-'.


'-': This value is populated when there are blank date values for either date1 or date2.
'#N/A': This value is populated when there are different dates in Column G. Is that correct?

[JeteMc]

Try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bdav1216]

One final question:
I have one scenario where Column G = blank but Column H = 03/23/2020. The date difference is outputting: 43913
Is there something that be updated to account for that scenario?

[JeteMc]

I can see that G897 is blank and that H897 is 3/23/2020, however I897 is displaying "-".
If that isn't the row in question, then it may be helpful to upload another file as it would make troubleshooting easier.

[bdav1216]

Thanks JeteMc,

I'm finishing up my review and only one other update I can see.

ID: 1172805 and Job-ID: Job-164644 are outputting a '-' and that is what I was expecting:

Column G has the same date: 4/29/2020
Column H has row field not present so the result was a '-'.

Is it possible to output the date difference for a scenario like this where, instead of outputting a '-', we would the difference if we can still pick one the earliest date in Column H to do the calculation? If the result is a negative value, we would still output a '-'.

Let me know if that is possible.

I don't see any other updates from my review

[JeteMc]

If I understand correctly then the following should work:

Please Login or Register  to view this content.

Let us know if you have any questions.

[bdav1216]

JeteMC - this is exactly what I was looking for.

My final request (i promise):

Can the same logic that is being used for all Shipment types (Column F) vs. just "interim" or "final"? The same rules that you have helped update would apply if there are illogical calculations, etc.

Thank you!

[JeteMc]

If I understand correctly then there may be terms in column F other than Interim and Final and all of them should be included.
If that is the case then try:

Please Login or Register  to view this content.

If the formula doesn't work then please upload another .xlsx file that illustrates the request (please include some manually calculated values with which we may compare the results of our proposed formulas/code).
Let us know if you have any questions.

[bdav1216]

Thanks JeteMc. There is an issue with the formula that I am seeing. I will update and send shortly.

[bdav1216]

Hello,
I have updated a new workbook with some samples highlighted. Column J has the formula prior to the last update and Column K has the newest formula (excluding the Types in Column F).

After reviewing the data, there are some data calculations with the new formula (Column K) that are excluding the calculation:

ID: 1178735
Job: Job-170234
Column K: -
Why would this value not output a value of 1? Both dates in Column H are after Column G.

Other examples:
ID: 1178734 / Job-170233


Also.....


ID: 1154375
Job: Job-147659
Column K: -
This is a new requirement, but don't know if it's possible, In a situation where Column K has a value of '-' because a value in Column H has a date that is prior to the date in Column G, resulting in a negative value, is it possible to review the other dates (if there are any) in Column H and use a date to do a calculation that is the same date or the closest to the date in Column G? For this example, instead of outputting a '-' because of a negative calculation, output a '1' if 03/02/20 - 03/01/20 is used?

Within the data there are other examples like this.
ID: 1155626 / Job: Job-150328
ID: 1162502 /Job: Job-164570 (should output a 2 if we can look at the closest possible date to Column G)
ID: 1164735 / Job-162759 (should output a 1 if we can look at the closest possible date to Column G)
ID: 1176230 / Job-167928 (should output a 4 if we can look at the closest possible date to Column G)
ID: 1198111 / Job-190535 (should output a 2 if we can look at the closest possible date to Column G)

Thanks!

[JeteMc]

As to ID: 1178735 Job: Job-170234 Column K: The formula in the file has an omission.
The formula reads:

Please Login or Register  to view this content.

The formula should read:

Please Login or Register  to view this content.

I'll look at the new request in a while if someone else has not resolved it beforehand.
Let us know if you have any questions.

[JeteMc]

As to the bottom of post #18 try:

Please Login or Register  to view this content.

Let us know if you have any questions.