# date difference (in days) with conditions

1. ## date difference (in days) with conditions

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 "-"  Register To Reply

2. ## Re: date difference (in days) with conditions

Hi @bdav1216

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)
Formula:  `Please Login or Register  to view this content.`  Register To Reply

3. ## Re: date difference (in days) with conditions

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)?  Register To Reply

4. ## Re: date difference (in days) with conditions

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)  Register To Reply

5. ## Re: date difference (in days) with conditions

Hi

You can try this formula:
Formula:  `Please Login or Register  to view this content.` Originally Posted by bdav1216 ... 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. Originally Posted by bdav1216 ... 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  Register To Reply

6. ## Re: date difference (in days) with conditions

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?  Register To Reply

7. ## Re: date difference (in days) with conditions

If I understand correctly then the following modification to José Augusto's formula eliminates the negative values in column I:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

8. ## Re: date difference (in days) with conditions

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.  Register To Reply

9. ## Re: date difference (in days) with conditions

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?  Register To Reply

10. ## Re: date difference (in days) with conditions

Try:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

11. ## Re: date difference (in days) with conditions

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?  Register To Reply

12. ## Re: date difference (in days) with conditions

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.  Register To Reply

13. ## Re: date difference (in days) with conditions

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  Register To Reply

14. ## Re: date difference (in days) with conditions

If I understand correctly then the following should work:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

15. ## Re: date difference (in days) with conditions

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!  Register To Reply

16. ## Re: date difference (in days) with conditions

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:
Formula:  `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.  Register To Reply

17. ## Re: date difference (in days) with conditions

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

18. ## Re: date difference (in days) with conditions

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!  Register To Reply

19. ## Re: date difference (in days) with conditions

As to ID: 1178735 Job: Job-170234 Column K: The formula in the file has an omission.
Formula:  `Please Login or Register  to view this content.`

Formula:  `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.  Register To Reply

20. ## Re: date difference (in days) with conditions

As to the bottom of post #18 try:
Formula:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply