Report

Future date based on text result and previu

Ask a question Littlemouse71 4Posts Sunday May 7, 2017Registration date May 11, 2017 Last seen - Last answered on May 11, 2017 at 06:35 AM by Littlemouse71
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
Helpful
+1
plus moins
see if this works:


=IF(N1="neg",EDATE(M1,12),IF(N1="pos",EDATE(M1,6),IF(N1>=15,EDATE(M1,6),EDATE(M1,6))))


I have assumed that "1.5E+1" is the same as 15. Change it to suit your needs. Also not sure where the 3 months you mention fits in.
Was this answer helpful?  
Littlemouse71 4Posts Sunday May 7, 2017Registration date May 11, 2017 Last seen - May 7, 2017 at 04:55 PM
Thanks - it doesn't work but I'm trying to tweak. It's certainly closer than I managed to get! It gave me 6 months for the "neg" even with the Edate(m1,12) for some reason. The 3 months is for another scenario which I probably shouldn't have included.

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!
Reply
x- May 8, 2017 at 11:03 AM
I think its adding 6 months (the last edate(M1,6) as default) as its not matching "neg" .
Maybe 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.
Reply
Littlemouse71 4Posts Sunday May 7, 2017Registration date May 11, 2017 Last seen - May 9, 2017 at 03:33 PM
Hi,
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
Reply
Leave a comment
Helpful
+0
plus moins
You were referencing column C for the last parameter. It should be B like below. This will remove the #NUM error


=IF(C5="Nég",EDATE(B5,12),IF(C5="Pos",EDATE(B5,6),IF(C5="Indétectable",EDATE(B5,12),EDATE(B5,6))))


The #VALUE error is a bit odd but re-entering the date in column B corrects this. It must be the date format used or like you suggest due to the machine it was creatde on.
Littlemouse71 4Posts Sunday May 7, 2017Registration date May 11, 2017 Last seen - May 11, 2017 at 06:35 AM
Ah, yes! I had re-entered all the dates in column B before reading this - and it works! thanks!!! It even worked as expected when I re-opened it on the older PC system (I had also saved it in an older format just in case).
Cheers!!!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!