Date format in Formula of type date

Posted by Rollbase User on 31-May-2012 21:28

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

All Replies

Posted by Admin on 01-Jun-2012 10:31

Options #js and #iso are available and work as expected.



Do you mean language-specific issues? Please explain.

Posted by Admin on 02-Jun-2012 07:14

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.

Posted by Admin on 02-Jun-2012 11:15

Can't confirm this: works as expected.



Please try reproduce in my test environment if problem persists.

Posted by Admin on 02-Jun-2012 16:01



I opened a support ticket with a reference to the Object ID of the template using the problematic date formula.





Posted by Admin on 02-Jun-2012 21:16

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

Posted by Admin on 02-Jun-2012 23:17

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.



Posted by Admin on 03-Jun-2012 11:13

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.

Posted by Admin on 03-Jun-2012 11:25

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.

Posted by Admin on 03-Jun-2012 19:24

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

Posted by Admin on 03-Jun-2012 20:31

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.

Posted by Admin on 03-Jun-2012 20:48

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

Posted by Admin on 03-Jun-2012 21:17

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.

This thread is closed