I have a Formula Date Field Titled "Tentative Discharge Date". I also have a dropdown field titled "Reason for Placement", and a Date field title Placement Date, I want the following to happen with the formula for Tentative Discharge Date If Reason for Placement=Customer had contact with known positive, Tentative Discharge Date is 14 days AFTER Placement Date If Reason for Placement=Customer is showing symptoms,Tentative Discharge Date is 7 days after Placement Date, if there is no value in Date COVID-19 Tests Results Received, otherwise expected discharge date is 7 days after Date COVID-19 Tests Results Received If Reason for Placement=Customer is positive,Tentative Discharge Date is 7 days after Date COVID-19 Tests Results Received :-)------------------------------Ermias Bean------------------------------

try thisIF([Reason for Placement] = "Customer had contact with known positive], [Placement Date] + Days(14),[Reason for Placement] = "Customer is showing symptoms" and IsNull([Date COVID-19 Tests Results Received]), [Placement Date] + Days(7),[Reason for Placement] = "Customer is showing symptoms" and not IsNull([Date COVID-19 Tests Results Received], [Date COVID-19 Tests Results Received] + Days(7),[Reason for Placement] = "Customer is positive", [Date COVID-19 Tests Results Received] + Days(7))------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Ermais,There is still a problem with the sequence of the logic checks, please sequence these properly as QuickBase will evaluate the tests in the order they appear. for example this is the second test, but what if the [Date of First Symptoms] is null? You see you need to deal with that possibility before you et to the 2nd text. If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after[Date of First Symptoms]so you will need to check for that first. If you would like one on one assistance in helping you think though the correct logic steps, contact me by email. If [Reason for Placement]="Customer had contact with known positive," [Tentative Discharge Date] is [14] days after [Placement Date] If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after [Date of First Symptoms] If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed] or if that is blank, [Tentative Discharge Date] is 10 days after the [Placement Date] If [Reason for Placement]="Customer is positive", Tentative Discharge Date is 10 days after Date of First Symptoms If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed or if that is Null, [Tentative Discharge Date] is 10 days after the [Placement Date]------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Please sequence you tests in the sequence they need to be done, but testing for those null date situations first and without the indenting.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

thanks Mark, I think I need to change the Tentative Discharge Date formula; it should be;If the field "Entrance Reason" = New Placement or customer changed room If [Reason for Placement]="Customer had contact with known positive," [Tentative Discharge Date] is [14] days after [Placement Date] If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after [Date of First Symptoms] If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed] or if that is blank, [Tentative Discharge Date] is 10 days after the [Placement Date] If [Reason for Placement]="Customer is positive", Tentative Discharge Date is 10 days after Date of First Symptoms If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed or if that is Null, [Tentative Discharge Date] is 10 days after the [Placement Date]------------------------------Ermias Bean------------------------------

There seems to be an omission in your description. If the Entrance Reason isn't New Placement or customer changed room AND Date of first Symptoms is NOT NULL - what is the tentative discharge date. Also is the are highlighted in orange intended to be dependent on the reason for placement (statement above it).If the field "Entrance Reason" = New Placement or customer changed room If [Reason for Placement]="Customer had contact with known positive," [Tentative Discharge Date] is [14] days after [Placement Date] If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after [Date of First Symptoms] If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed] or if that is blank, [Tentative Discharge Date] is 10 days after the [Placement Date] If [Reason for Placement]="Customer is positive", Tentative Discharge Date is 10 days after Date of First Symptoms If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed or if that is Null, [Tentative Discharge Date] is 10 days after the [Placement Date]Is it possible to create a table with the tentative release date and the conditions which would cause that date, for example:14 days after Placement: Customer contact with known positive10 days after Placement: Customer showing symptoms and no date of first symptoms and no Covid test date10 days after First Symptoms: Customer is Positive...Let me know if you can - happy to help..------------------------------Neil Schneider------------------------------

Formula Date field based on conditions

30 Replies

MarkShnier__You
Qrew Legend
6 years ago
try this

IF(
[Reason for Placement] = "Customer had contact with known positive], [Placement Date] + Days(14),
[Reason for Placement] = "Customer is showing symptoms" and IsNull([Date COVID-19 Tests Results Received]), [Placement Date] + Days(7),
[Reason for Placement] = "Customer is showing symptoms" and not IsNull([Date COVID-19 Tests Results Received],
[Date COVID-19 Tests Results Received] + Days(7),
[Reason for Placement] = "Customer is positive", [Date COVID-19 Tests Results Received] + Days(7)
)

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- Del
  Qrew Cadet
  5 years ago
  thanks Mark, I think I need to change the Tentative Discharge Date formula; it should be;
  
  If the field "Entrance Reason" = New Placement or customer changed room
  
  If [Reason for Placement]="Customer had contact with known positive," [Tentative Discharge Date] is [14] days after [Placement Date]
  
  If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after [Date of First Symptoms]
  
  If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed] or if that is blank, [Tentative Discharge Date] is 10 days after the [Placement Date]
  
  If [Reason for Placement]="Customer is positive", Tentative Discharge Date is 10 days after Date of First Symptoms
  
  If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed or if that is Null, [Tentative Discharge Date] is 10 days after the [Placement Date]
  
  ------------------------------
  Ermias Bean
  ------------------------------
  - NeilSchneider
    Qrew Cadet
    5 years ago
    There seems to be an omission in your description.
    If the Entrance Reason isn't New Placement or customer changed room AND Date of first Symptoms is NOT NULL - what is the tentative discharge date.
    Also is the are highlighted in orange intended to be dependent on the reason for placement (statement above it).
    If the field "Entrance Reason" = New Placement or customer changed room
    
    If [Reason for Placement]="Customer had contact with known positive," [Tentative Discharge Date] is [14] days after [Placement Date]
    
    If [Reason for Placement]="Customer is showing symptoms", [Tentative Discharge Date] is 10 days after [Date of First Symptoms]
    
    If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed] or if that is blank, [Tentative Discharge Date] is 10 days after the [Placement Date]
    
    If [Reason for Placement]="Customer is positive", Tentative Discharge Date is 10 days after Date of First Symptoms
    
    If [Date of First Symptoms] is Null, [Tentative Discharge Date] is 10 days after [Date COVID-19 Test Completed or if that is Null, [Tentative Discharge Date] is 10 days after the [Placement Date]
    
    Is it possible to create a table with the tentative release date and the conditions which would cause that date, for example:
    14 days after Placement: Customer contact with known positive
    10 days after Placement: Customer showing symptoms and no date of first symptoms and no Covid test date
    10 days after First Symptoms: Customer is Positive
    ...
    Let me know if you can - happy to help..
    
    ------------------------------
    Neil Schneider
    ------------------------------

Forum Discussion

Formula Date field based on conditions

30 Replies

Related Content

Conditional formula url

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Snapshot Fields

Color Code Date Field based on Formula

Formula Url Buttons