Has anybody written a function that will translate a Date Formula to text? Say to place on a Sales Invoice or Quote. I’ve included a line saying when the product will be available and calculating the date relative to the Header Date, but just putting the Item Lead Time Calculation in text I think would be of more value.
Have you looked at the CALCDATE function? MESSAGE(‘One week from now, it is %1’,CALCDATE(’+1W’,TODAY)); shows that will be 3rd August one week from today. John
That’s how I got the date to show up on the Quote. The problem is the message that must be assigned to that date so the client’s customer understands it is “The items will not be delivered any earlier than…” and then attach the CALCDATE relative to the Order Date. What I want to do is take the lead time and translate it into words so that the message to the customer is “The items will be delivered” add the Date Formula decode here “after this quote is confirmed”. The decode will take the Date Formula (say 2W) and change it into text (2 weeks). This sounds pretty simple until you start looking at some of the combinations that can occur. Like the supplier only ships by ground on Wednesdays, so orders are never received until Fridays. cheers,
I used this to calculate a warranty from a DateFormula that held something like 1Y+6M or 2D etc, but it doesnt handle weeks, you need to use the DATE2DWY formula and subtract the week numbers for that I suppose. The resulting textual description ends up in ‘MSG’. End:=CALCDATE(DateFormula,010101D); Years:=DATE2DMY(End,3)-DATE2DMY(010101D,3); Months:=DATE2DMY(WtyEnd,2)-DATE2DMY(010101D,2); Days:=DATE2DMY(WtyEnd,1)-DATE2DMY(010101D,1); MSG:=’’; //Calculate Years IF WtyYears>0 THEN BEGIN IF MSG<>’’ THEN WtyAdd:=’ ’ ELSE WtyAdd:=’’; WtyAdd:=WtyAdd+STRSUBSTNO(’%1 Year’, WtyYears); IF WtyYears>1 THEN WtyAdd:=WtyAdd+‘s’; MSG:=MSG+WtyAdd; END; //Calculate Months IF WtyMonths>0 THEN BEGIN IF MSG<>’’ THEN WtyAdd:=’ ’ ELSE WtyAdd:=’’; WtyAdd:=WtyAdd+STRSUBSTNO(’%1 Month’, WtyMonths); IF WtyMonths>1 THEN WtyAdd:=WtyAdd+‘s’; MSG:=MSG+WtyAdd; END; //Calculate Days IF WtyDays>0 THEN BEGIN IF MSG<>’’ THEN WtyAdd:=’ ’ ELSE WtyAdd:=’’; WtyAdd:=WtyAdd+STRSUBSTNO(’%1 Day’, WtyDays); IF WtyDays>1 THEN WtyAdd:=WtyAdd+‘s’; MSG:=MSG+WtyAdd; END; Craig Bradney Project Manager - Technical Navision Solutions & Services Deloitte Growth Solutions Deloitte Touche Tohmatsu P:+61-2-9322-7796 F:+61-2-9322-7502 E:craig_bradney@deloitte.com.au
change the top two lines where it had the word End to WtyEnd and then itll be ok… I just mistyped them… Craig Bradney Project Manager - Technical Navision Solutions & Services Deloitte Growth Solutions Deloitte Touche Tohmatsu P:+61-2-9322-7796 F:+61-2-9322-7502 E:craig_bradney@deloitte.com.au
Thanks Craig! It was very helpful. I knew I didn’t need to invent the wheel. cheers,