Change Date format to YYYY/MM/DD

nav_student · 2014-08-04

Hi guys,

i want to change the format of date to DD/MM/YYYY to YYYY/MM/DD.

How can i accomplish that?

Thanks for your help!

Reinhard · 2014-08-04

FORMAT(dateValue,10,'<Year4>/<Month,2>/<Day,2>');

Rishi1109 · 2014-08-05

It takes the Regianal Setting from your OS .
In Windows go to Control Panel --> Region and Language
Change the Format there

nav_student · 2014-08-05

Hi Reinhard,

thanks for your reply. the code

FORMAT(dateValue,10,'<Year4>/<Month,2>/<Day,2>');

is not working.

If I try with "Year,4"

FORMAT(dateValue,10,'<Year,4>/<Month,2>/<Day,2>');

the output is 0014/06/01 instead of 2014/06/01

i try to use this commands but isn't 100% accurate:

datestring:=FORMAT("Posting Date",10,'<Day,2>/<Month,2>,<Year4>');

datestringresult:='_'+COPYSTR(datestring,7,10)+'/'+COPYSTR(datestring,4,2)+'/'+COPYSTR(datestring,1,2);

but if i put / symbol or - Excel will put the date with "DD/MM/YYYY" automatically.

How can i solve this?

TonyDuarte · 2014-08-05

You can try to format your date with the following: FORMAT(TODAY,0,'<Year4>/<Month,2>/<Day,2>')

nav_student · 2014-08-05

Hi Tony,
i already do this but Excel converts into DD/MM/YYYY.

Can i setup the Excel Buffer to work with this format YYYY/MM/DD?

Best Regards

Reinhard · 2014-08-05

Hello sir,

It is working for me

(But I don't use Year,4 I'm using Year4)

When you say it's not working, can you be more specific?

You mention that Excel is reformatting your date. That is true, Excel will do that. You can change the formatting in Excel.
To get an idea of how to do that, you can search the forum for topics related to it.
http://www.mibuso.com/forum/viewtopic.p ... 91&start=0

Also, the trick is, open Excel, then "record a new macro" and then format everything how you want it.
Next, go ahead and edit the macro. It will show you the code to format everything how you want it.
Now, you can replicate the same code in NAV.

In this case, you should just go ahead and export the date value without formatting it. Then, tell excel to:

NumberFormat = "yyyy/mm/dd"

hth

- Reinhard

vaprog · 2014-08-05

Use the NumberFormat field to control how Excel displays dates.
The value of the field is copied to Excel's cell property NumberFormat.
Number Format Codes may give you some idea of the codes available. Or use Google to find more info how to set this property. It is easier to find it there than in Excel's own help.

nav_student · 2014-08-07

ExcelBuffer.NumberFormat:='yyyy-mm-dd;@';

also try this code

ExcelBuffer.NumberFormat:='yyyy-mm-dd';

with this

ExcelRowData[5] := FORMAT("Posting Date",10,'<Year4>/<Month,2>/<Day,2>');

and the Excel value gives me 05/06/2013

Savatage · 2014-08-07

NewDateText := FORMAT("Posting Date",0,'<Year4>/<Month,2>/<Day,2>');

Works fine for me too. I don't know how it's returning the value 05/06/2013.

Play around with it, review the results, try quotes in front if excel is dividing the #'s:

''''+FORMAT("Posting Date",0,'<Year4>/<Month,2>/<Day,2>');

nav_student · 2014-08-08

I sorted it by doing this:

ExcelRowData[5] := FORMAT("Posting Date",11,'<Year4>/<Month,2>/<Day,2>');

kriki · 2014-09-18

[Posts from topic http://www.mibuso.com/forum/viewtopic.php?t=62159 have been merged into this topic]

Howdy, Stranger!

Quick Links

Categories

Change Date format to YYYY/MM/DD

Comments