+ Reply to Thread
Results 1 to 20 of 20

date difference (in days) with conditions

  1. #1
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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 "-"
    Attached Files Attached Files

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: date difference (in days) with conditions

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

  3. #3
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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)?

  4. #4
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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)

  5. #5
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: date difference (in days) with conditions

    Hi

    You can try this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Quote Originally Posted by bdav1216 View Post
    ... 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.
    Quote Originally Posted by bdav1216 View Post
    ... 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
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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?
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    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: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Last edited by JeteMc; 10-27-2020 at 01:18 PM. Reason: edited text
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  8. #8
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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.

  9. #9
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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?

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: date difference (in days) with conditions

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

    Let us know if you have any questions.

  11. #11
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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?

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    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.

  13. #13
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: date difference (in days) with conditions

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

    Let us know if you have any questions.

  15. #15
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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!

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    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: copy to clipboard
    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.

  17. #17
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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.

  18. #18
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    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!
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: date difference (in days) with conditions

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

    The formula should read:
    Formula: copy to clipboard
    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.

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,285

    Re: date difference (in days) with conditions

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

    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lookup with date difference in days?
    By cdhamo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-02-2019, 04:09 AM
  2. [SOLVED] Summing with date difference and conditions
    By ZedaG in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-23-2017, 12:26 PM
  3. Calculate Date Difference but Business Days Only
    By lborja in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2016, 12:28 PM
  4. [SOLVED] Need to add Date difference of 15 days
    By sriku in forum Excel General
    Replies: 1
    Last Post: 05-06-2014, 12:51 AM
  5. Add Date in difference of 15 days
    By sriku in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2014, 12:30 AM
  6. Replies: 15
    Last Post: 06-26-2012, 07:28 AM
  7. Date Difference Countdown (Days to go & Passed)
    By Merlin54k in forum Excel General
    Replies: 1
    Last Post: 03-27-2008, 08:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1