Future date based on text result and previu
Closed
Littlemouse71
Posts
4
Registration date
Sunday May 7, 2017
Status
Member
Last seen
May 11, 2017
-
May 7, 2017 at 12:55 PM
Littlemouse71 Posts 4 Registration date Sunday May 7, 2017 Status Member Last seen May 11, 2017 - May 11, 2017 at 06:35 AM
Littlemouse71 Posts 4 Registration date Sunday May 7, 2017 Status Member Last seen May 11, 2017 - May 11, 2017 at 06:35 AM
Related:
- Future date based on text result and previu
- Result formula in excel marksheet - Guide
- Your account will remain banned as a result of the violation - WhatsApp Forum
- Ping result to text file - Guide
- We couldn't find an account matching the login info you entered, but found an account that closely matches based on your login history ✓ - Facebook Forum
- Based on the values in cells b77 b81 ✓ - Excel Forum
2 responses
see if this works:
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.
=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.
You were referencing column C for the last parameter. It should be B like below. This will remove the #NUM error
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.
=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
Posts
4
Registration date
Sunday May 7, 2017
Status
Member
Last seen
May 11, 2017
May 11, 2017 at 06:35 AM
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!!!
Cheers!!!
May 7, 2017 at 04:55 PM
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!
May 8, 2017 at 11:03 AM
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.
May 9, 2017 at 03:33 PM
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://onedrive.live.com/redir?resid=7D13DFDDBB5D8EE!104&authkey=!AIFE5WpO9COOaGU&ithint=folder%2c should work....
cheers