• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help Figuring out what Formula is right?

L

Lorraine Jean

New Member
Hi!

I'm struggling to figure out how to do this problem or at least initiate it.

A person is supposed to conduct a GC/CHL test once every 30 days after their Collection Date (Collection Date + 30 days).

If they come for a visit (Date of Visit) during a time that is greater than or equal to 30 days to their last collection date, then that should be flagged. For an example, D17 - Tanya had a Days Elapsed of 87 days (57 days past her test date) so I flagged this in my head. Tanya could visit without taking the test until 8/29 (7/29 + 30 days). 8/29 would require her to test again. However, in the table you'll notice that Tanya came in on 9/5, 9/10, 9/18 & 9/24 without taking a GC/CHL test. Brit is good. Even though Brit had 149 days elapsed, it's good because she did not visit until during 5/9 without taking the test.

How do I appropriately flag this? Not sure what formulas to start with....

Thank you so much! Hope I explained this correctly.
 

Attachments

  • Confused Example.xlsx
    10.8 KB · Views: 10
Hi Lorraine,

I think you should only work with the latest collection and ignore any earlier ones, because the 30 period starts from that date

cheers

kanti
 
Hi, Lorraine Jean!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, I'd flag every entry in columns A:E, maybe highlighting the last entry with other color or format. BTW, how do you want to get the flag displayed? As a true/false, a zero/non-zero, as a day count, as a string "yes"/"no", ... all in column E. Besides the value of column E, do you intend to user any CF condition (conditional formatting)?

Regards!
 
Hi SIrJB7,

Thank you for your response. It's not the very last collection date that always has the most concern. it's the Days Elapsed between any collection dates greater than 30 that I need to flag but only flag if they had not come in to Date of Visit during that time.

I *hope* that makes sense.
 
Hi, Lorraine Jean!

2 things.

1st, I think I don't fully understand your problem, so consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

2nd, it's regarding the DATEDIF function. I think you should read this:
http://chandoo.org/forum/threads/excel-2007.3402/

Regards!
 
Hi SirJB7,

I used datedif in the "days elapsed" column and that was helpful. I've uploaded a new file with manual examples of desired outcome. I know it's hard to explain for me but I am trying my best. Thank you for being patient.
 

Attachments

  • Confused Example.xlsx
    11.3 KB · Views: 4
Hi, Lorraine Jean!

1st, a simpler formula for column D:
D5: =SI(B5=B6;A6-A5;$D$2-A5) -----> in english: =IF(B5=B6,A6-A5,$D$2-A5)
no need of any date function, hopefully in this case regarding the DATEDIF issues, as Excel stores dates as integer values you can apply arithmetic and operate with them.

2nd, I still don't understand the flagging method. In column F, I placed my first attempt for a formula but it differs for rows 8, 10, 15 & 19. BTW, why row 8 is good, if D8 is 28, less than 30?, same for row 15 and row 19, and why row 10 is good, if D8 is 149, greater than 30?

Regards!
 
Hi, Sir JB7,

Wow, I learned something new from the simpler formula. Thanks so much!

What I'm dealing with is that these girls need to do GC/CHL testing every 30 days if they are coming in to Visit. I actually want "30" (D1) to be interchangeable it case I need to fix that number.

Row 8 (D8) - is good because Britt came into do her test for GC/CHL one month apart (2/4/2013-3/4/2013). I should clarify, it can be <=30 days so 28 would be good because it's under 30.

Row 10 - Row 10 is good ( i made a mistake in the worksheet). Even though Britt went 149 days from 4/9 to 9/5, she never came back to visit except on 4/11 (4/11 is ok b/c it's in 30 day range since 4/9)

Row 15 - under 30 days, good

Row 19 - it's only been 4 days from 12/6-12/10, so good.

I've uploaded the file with more remarks in the Desired Output! I seriously appreciate this!)
 

Attachments

  • Confused Example.xlsx
    11.7 KB · Views: 5
Hi, Lorraine Jean!
Just a follow up... it this finally solved? If I don't remember wrong I was wondering about a COUNTIFS solution too...
Regards!
 
Hi, Lorraine Jean!
My idea for the COUNTIFS solution was long too, I just wanted to know if had to work a bit more, but hopefully not, do I?
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top