Formula assistance for custom transaction field.


Our production process is tied to two dates on our sales order.  A Build Date  (When a car goes into production) and a Target Date (when it should be delivered to the customer).

Due to supply shortages, we would to keep the two dates for our production team as is but create a custom field that will give a “Delivery Window ” that would be presented to the customer, based on the Target Date.

The requirement  is to have the Delivery Window Show

  1. If the Target date is within the 1st -15th of the month  The window would be 1-15 (month of  the Target Date) 1-15 May
  2. If the  Target date is within the 16th -31st of the month  The window would be 16 -31 (month of  the Target Date)  16-31 Aug

Any assistance is greatly appreciated.

Thank you


Beginner Asked on November 16, 2021 in Administration.
Add Comment
4 Answer(s)

Could you do something like the below? Of course, as written, the ELSE will return data that is misleading/incorrect for months that don’t have 31 days in them. You could account for that in your formula, it just gets messier.


CASE WHEN TO_NUMBER(TO_CHAR({targetdate}, ‘DD’)) <= 15 THEN ‘1-15 ‘ || TO_CHAR({targetdate}, ‘MON’) ELSE ’16-31 ‘ || TO_CHAR({targetdate}, ‘MON’) END

Rookie Answered on November 17, 2021.
Add Comment

Thank you for your help,

We’ll give it a try.


Beginner Answered on November 17, 2021.
Add Comment

ended up going with : case when EXTRACT(DAY FROM {custbody_target_delivery_date}) > 15 then concat(‘Second Half Of’||’ ‘, To_Char({custbody_target_delivery_date},’MON’)) else concat(‘First Half Of’||’ ‘, To_Char({custbody_target_delivery_date},’MON’)) end



Beginner Answered on February 17, 2022.
Add Comment

Then they wanted three windows. First of, Mid of and End of.  So we went with:

case when EXTRACT(DAY FROM {custbody_target_delivery_date}) > 20 then concat(‘End of’||’ ‘, To_Char({custbody_target_delivery_date},’MONTH’))
when EXTRACT(DAY FROM {custbody_target_delivery_date}) < 10 then concat(‘Beginning of’||’ ‘, To_Char({custbody_target_delivery_date},’MONTH’))
else concat(‘Middle of’||’ ‘, To_Char({custbody_target_delivery_date},’MONTH’)) end

Beginner Answered on May 5, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor