When a formula stores a date, the return date string is dependent of the user's preferences format. The cause a problem when using the formula in javascript as the month and the day and year can be miss interpreted in certain cases. Please add a way to do the following : var d1 = new Date("{!formula_field#js}"); var d2 = new Date("{!formula_field#iso}"); Thanks
Options #js and #iso are available and work as expected.
Do you mean language-specific issues? Please explain.
I used the #iso tag on a date formula within a doc template.
It returned a string that wasn't iso formatted...
I can reproduce.
Can't confirm this: works as expected.
Please try reproduce in my test environment if problem persists.
I opened a support ticket with a reference to the Object ID of the template using the problematic date formula.
Hi Claude,
Why not just use the #iso suffix within the formula field?
i.e, return "{!date1#iso}"; as a string in a formula field - formula1
then just use, new Date('{!formula1}'); in your next trigger or formula body.
if you need another format like #js just create another formula field containing #js instead. i.e formula2 with return date1#js.
This way you get the desired format.
Hope this helps.
Piscoso, Martin
Rollbase
I do some time calculation on the date in the formula.
The result is a javascript Date object as required by the formula type.
As you suggested, using 2 or 3 formulas of type string could be a workaround.
But I already bypassed the problem by writing my own "#iso" function to process the problematic formula.
Rollbase seams to translate and store the date output in the locale format "DD/MM/YY". Then when the formula is recall in another formula or template, the #iso and #js suffix don't get applied as expected.
I just hope that eventually no workaround will be required.
DD/MM/YYYY is European presentation date format which (I assume) you and your users have selected. This format should be used for presentation only, not for formulas.
If you see that format DD/MM/YYYY is used in formulas that would be a bug. But I don't see this happening. I just tried the following formula:
var d = new Date("{!my_date}");
d.setMonth(4);
return d;
If you debug this formula it will provide the following output:
001 function wrapper() {
002 var d = new Date("Fri Jun 01 2012");
003 d.setMonth(4);
004 return d;
005 }
006 wrapper();
As you can see JavaScript date format is used - regardless of user's selected presentation format.
I trust that you're facing a problem but I'm unable to help based on your description.
Please note that I do not use either #js or #iso (although they work as expected). The code is intelligent enough to pick JavaScript Date format for formulas automatically.
Hi Pavel,
I think what he means that if he creates a Formula Field with a date return type it uses the user's presentation format,
i.e.
Date Field - {!my_date}
Formula Field - {!my_formula}
Formula body - return new Date("{!my_date}");
- When the {!my_formula} renders, it uses the presentation format MM/DD/YYYY
- He wants the choices #iso and #js in Formula Fields returning dates as well.
Something like {!my_formula#js} and {!my_formula#iso} for date return formula fields.
This is why I suggested him to use two separate formula fields as strings,
i.e.
{!my_iso_date} - contains return "{!my_date#iso}";
{!my_js_date} - contains return "{!my_date#js}";
But from reading his above statements, he did some calculations within the formula field which returns a JavaScript date object.
This is why he would need the #iso and #js suffixes for formula fields.
e.x.
Formula Body - {!new_formula}
var x=new Date();
x.setDate(x.getDate() + 1);
return x;
When {!new_formula} renders, it will s
This is correct.
This is what I am experiencing.
I made some additional tests, and the "DD/MM/YYYY" format only appeared when the formula is called from a template. In other cases like triggers, the same formula returned as "Wed May 04 2011".
However, in both cases, I wasn't able to make the #iso suffix work on the formula.
Hi,
With regards to >> when the formula is called from a template.
- This is now rendered client side which I believe falls under the scope of presentation format as stated by Pavel, since you are already "viewing" the data. Hence it returns as "DD/MM/YYYY" and as a javascript date when used in the formula body of triggers and other formulae.
- There is no #iso and #js suffix for formula fields 'yet' and I'm not so sure if this will be needed since they are already available for regular date fields.
- Here are some options you may want to take a look at:
1. You can save the formula field onto a regular date field to take advantage of the #iso and #js extensions.
2. If you cannot do #1 - maybe due to dynamic data on template - you can alternatively use a string return formula and convert your JavaScript native date to ISO format via code: possibly something like:
/* use a function for the exact format desired... */
function ISODateString(d){
function pad(n){return n
OK, Martin, thank you for explaining to me what Claude meant.
#iso and #js are not available for Formula fields of type Date, but they actually work as expected.