+ Reply to Thread
Results 1 to 3 of 3

Need to find the number of dates between two repair dates with condition

  1. #1
    Registered User
    Join Date
    06-11-2018
    Location
    Qatar
    MS-Off Ver
    2013
    Posts
    12

    Need to find the number of dates between two repair dates with condition

    Dear All

    I have attached the sample data sheet along with the desired result wish to
    achieve. second page is having the actual repair records which i need to apply the formula.

    what want to find as follows.

    when any serial number comes to service, the formula should look in to repair history of that serial number. if there is any previous record(S) relates to respective serial number, formula should calculate the number of date between the recieved date and the previous finished date with one conditon. repair stutus of previous repair equal to Completed, then Repeated status should be "Yes", if repair status equal to "cancelled" repeated status should be "No"

    Serial Number- Received date- Finished date- Repair Status- # of dates between previous repair- Repeated status
    738 5/1/2018 5/4/2018 Completed 0 No
    738 5/7/2018 5/10/2018 Cancelled 3 Yes
    738 5/15/2018 5/18/2018 Completed 5 No
    885 5/15/2018 5/18/2018 Cancelled 0 No
    885 5/26/2018 5/29/2018 Completed 8 No
    885 8/12/2018 8/15/2018 Completed 75 Yes
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Need to find the number of dates between two repair dates with condition

    3700 rows isn't a sample. It makes manual checking a nightmare!!

    This sample covers all your bases, I think.

    V2, copied down:
    =IFERROR(S2-LOOKUP(2,1/(C2=$C$1:C1),$S$1:$S1),"")

    W2, copied down:
    =IFERROR(IF(LOOKUP(2,1/(C2=$C$1:C1),U$1:U1)="Completed","Yes","No"),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    06-11-2018
    Location
    Qatar
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to find the number of dates between two repair dates with condition

    Dear Glenn

    all I can say, you are brilliant, it helps me a lot. would you please tell me how i can improve the same formula with one additional condition as follows?
    I want to match the affected product as well. because two different affected product can have same serial number which would not make the 100% accurate result.

    match the serial number+ affected product+Status

    thanks a lot

+ 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. Replies: 8
    Last Post: 07-20-2018, 11:52 AM
  2. [SOLVED] Need to Find a Number between dates
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-09-2018, 10:22 AM
  3. [SOLVED] Need to find the date between two other dates with one condition
    By hazeljohn19 in forum Excel General
    Replies: 2
    Last Post: 12-12-2017, 12:25 AM
  4. [SOLVED] Condition Formula between dates then sum if meets between those dates
    By rhett7660 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-30-2017, 03:52 PM
  5. Replies: 4
    Last Post: 02-17-2015, 08:15 AM
  6. Condition formating on due dates between a row of dates
    By irismarcial in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-11-2012, 04:18 PM
  7. Adding a formula that calculates dates by repair hours
    By dave1967 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2009, 03:35 PM

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