Nested If Then Else Statement based on Date Issues

Question

I have a file that contains a list of all products. However, whenever there's a price change to products the same list gets generated but with new updated prices that overrides the older price. So the effective start date lets me know if the prices have changed. I want to create a workflow that can only pull data where 'effective start date' matches today's date to populate that product's information into the final output.

I have tried using formulas shown below but Price shows as '0' and effective date as 'null'

Please advise

Sample Data:

Price List Name                Product Number    Currency            Price          Start Date     End Date

(string)

US Commercial Products    210-595-2222         USD                   $1000         6/21/2017   12/31/2017

US Commercial Products    333-595-2221         USD                   $500           1/1/2017     12/31/2017

US Commercial Products    210-595-2221         USD                   $250           6/21/2017    12/31/2017

US Commercial Products    333-595-2223         USD                  $1150          11/1/2016    12/31/2017

The final file must be in the following format that contains the following fields and following order:

Mfg Name (leave blank)      Product ID     Product IDQualifier (Leave blank)     Price      Effective Date

Sample Output based on above data:

Mfg Name (leave blank)      Product ID     Product IDQualifier (Leave blank)     Price      Effective Date

210-595-2222                                                               $1000     06/21/2017

210-595-2221                                                               $250       06/21/2017

I'm using the following formulas to populate the price and the effective date fields in formula tool:

ProductID: IF [Start Date]=DateTimeNow() Then [Product Value] Else "" Endif

ProductPrice: IF [Start Date]=DateTimeNow() Then [Price] Else "" Endif

EffectiveDate: IF [Start Date]=DateTimeNow() Then [Start Date] Else "" EndIf

Philip · Accepted Answer

Hi @arnorian

The reason the formula doesn't work is because you used DateTimeNow() instead of DateTimeToday(). DateTimeNow() will return today's date along with the time it is generated. The times won't match.

Here is how I created the output you wanted. First, I used a Filter Tool to find those that are effective today, then a Formula Tool to create the blank fields.

Let me know if you have any questions or additional problems.

Philip · Accepted Answer

And like @NicoleJohnson said, make sure that the start date and end date are Date data types.

rohanonline · Accepted Answer

Try the attached workflow. This should work. Let me know if you still have any requirements.