Hello,

I'm not very good at this and am struggling to find the correct formula to calculate future dates but with multiple variables and cells as conditions... I need to provide the information tomorrow! Please can someone help?

Column M contains dates certain activities were carried out

Column N contains text related to the results (neg, pos, or an actual number)

Column AP needs to contain a new date either 3 , 6, or 12 months in the future based on the text contained in N

If N is "neg" add 12 months

If N is "pos" or a any number other than "<1.5E+1", add 6 months

If N is "<1.5E+1" add 6 or 12 months - ideally being able to choose between the 2 dates, otherwise leave as 6 months).

I am soooo grateful for any help possible!

Kind regards,

KH

Basically, I need to track when to follow up a particular group of patients:

- Blood tests taken on date 'M1'

- Results in 'N1'

- If négative, no follow up for 1 year

- If <1.5E+1, follow up in 6 months is usually needed as it's a subset that had Hepatitis C but have been 'cured' theoretically (in some cases we can push to 12 months but for the sake of this, it will be 6 for all! (The 3 months is if they also have HIV...)

- if it's positive or reported with a numerical value of >1.5E+1 (the value shows the activity of the virus in their body), it has serious health implications if we don't take action again within a particular time frame as they are at high risk of developing liver cancer.

So, all the 'positives' and '>1.5...' also have Ultrasounds 1-2 x/year and other exams. However, it all starts with the blood test. I can't keep track of who needs what when without some form of dynamic dbase and my dept won't invest.

So thank you!

x- May 8, 2017 at 11:03 AMMaybe there is a trailing space like "neg ".

Maybe its "Neg"

Change the last edate to 24 months (or some other number of months not expected) to prove this theory.

Maybe post your data so we can see what you have. Remove names etc. before doing so.

I thought I'd found a way.. but then I came home and used my computer and it's all wrong again. At work it's PC's and an older version of Excel - at home, a Mac and latest version.

And, yes, I had also tested your correct assumption, prior to reading your reply by inputting various months in the end! It also doesn't seem to like the '<1.5E+1 ' so I changed it to 'indétectable'. I have to this as <1.5E+1 is different from 1.2E+7, for example, and requires different actions.

It weirdly works for some of the rows, but not others. I don't know how to fix this but am wondering if it's due to which machine the data was input on.

Would you mind looking at the formula?? Some blank cells in M (now F) return a date of 30.06.00 and others with text not in the formula, gives #VALUE!

Some Indétectable give the correct 12months, others give #VALUE!

All rows, including the first which is my test row, gave a date in the past when I entered a different value (e.g., 5.5E+7).

I used the date formula in M and in the results column but formatted the cells as text in N.

I'm pulling my hair out now!

https://1drv.ms/f/s!Au7Ytdv9PdEHaIFE5WpO9COOaGU should work....

cheers