Formula is not working

[Sanjana_HS]

Hi,

Can anyone please help how to make this formula work. I have attached excel for the desired output.

[phottovy · Accepted solution]

Hi @Sanjana_HS , at first glance, the "SIRVA Date" column doesn't appear to have any empty values:

When I try to replicate your formula, every condition after "ELSEIF !IsEmpty([SIRVA Date]) THEN "SIRVA" is skipped because it is assigning "SIRVA" to all of the values that didn't meet the first several conditions. I tried cleaning your data to remove the "00:00:00" values. Now many of the columns have null values in both of the date columns which makes them meet your first condition "IsEmpty([SIRVA Date]) AND IsEmpty([ILH No Relo Date])" and gives them the value "Add to ILH No Relo Tracker"

The formula will try the conditions in the order you list them so you might need to move some of the conditions above the IsEmpty() condition to see some of your desired results.

[Sanjana_HS]

Hi @phottovy 

Thank you so much. Reordering the conditions helped me. If you can help me with one more doubt i will be so thankful.

I am trying to achieve this:
If proposed Hire date coloumn is having dates from November or after,can be any date of November then I want in "SIRVA/ILH No Relo Report" coloumn to Print "too far out".

Attached excel file below

[phottovy]

You should be able to add a condition like this to your formula:

ELSEIF ToDate([Proposed Hire Date]) >= "2023-11-01" THEN "Too Far Out"

I don't normally recommend hard-coding a date value in a formula. To make the formula more dynamic, you can use a date calculation to look ahead 3 months (I made an assumption based on November) and write the condition this way:

IF ToDate([Proposed Hire Date]) >= DateTimeAdd(DateTimeFirstOfMonth(), 3, 'months') THEN "Too Far Out"

The "DateTimeAdd(DateTimeFirstOfMonth(), 3, 'months')" section of the formula calculates the first of the month from 3 months in the future.