Date Format in Reports in GP

[Gerald]

Is there a way to force the Date formats in the report to use dd/mm/yyyy or
use the Name of the Month/dd/YYYY in the DEX.ini?

[Mariano Gomez]

Assuming you don't want to change the date format under Regional and Language
options in your Control Panel, then the only other choice is to create
calculated fields. You can use the DAY(), MONTH(), and YEAR() Report Writer
system functions to pull apart the date field into individual calculated
fields. For example:

(C) Day = DAY(RM_Open.Document Date)
(C) Month = MONTH(RM_Open.Document Date)
(C) Year = YEAR(RM_Open.Document Date)

You will then need to create calculated fields that will convert the
previous ones to string again using the INT_STR() system function, for
example:

(C) StrDay = INT_STR((C) Day)
(C) StrMonth = INT_STR((C) Month)
(C) StrYear = INT_STR((C) Year)

Assuming you want to pad the month and the day with leading zeroes, you can
instead use the RW_PadZero user-defined function instead of the INT_STR(),
for example:

(C) StrDay = Function_Script(RW_PadZero (C) Day, 2, 0, 0)
(C) StrMonth = Function_Script(RW_PadZero (C) Month, 2, 0, 0)

NOTE: I use commas for readability sake, but these are not part of the
function.

Finally, you will need to concatenate the string calculated fields into one
single string, something like:

(C) StrDay # "/" # (C) StrMonth # "/" (C) StrYear

NOTE the laboriousness of doing this just for one date field. If you have
the need to print reports in dd/mm/yyyy format, you are better off changing
the date format in the Regional and Language options in your Control Panel,
temporarily, while printing your report, then revert back when done, assuming
this is a one-off type of situation.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com

[Gerald]

Thanks Mariano! this is good stuff. I was hoping that in the DEX.INI i can
force GP to use the Date Format we wanted. Modifying it on a per report
will be a nightmare.
Again Thank you

[Mariano Gomez]

There is actually a setting for the DEX.INI, but it's to override any other
date format to display mm/dd/yyyy format or American format.

[Leslie Vail]

The Dex.ini setting is:

StdInternationalInfo=TRUE

[David Musgrave [MSFT]]

I know this would need to be changed in each report, but you could try using

RW_DateToString() in a calculated field

in date IN_Date;
in string IN_Format;

{
Format Syntax:-
ddd = day of week as 3 letters
dddd = day of week in full
N = day of year
NN = day of year (padded)
D = day of month
DD = day of month (padded)
M = month of year
MM = month of year (padded)
mmm = month of year as 3 letters
mmmm = month of year in full
YY = year of date (2 digits)
YYYY = year of date (4 digits)
}

Also, you can change the regional settings as that is where the default format for dates comes from.

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David.M...@online.microsoft.com
http://blogs.msdn.com/DevelopingForDynamicsGP

Any views contained within are my personal views and not necessarily Microsoft policy.
This posting is provided "AS IS" with no warranties, and confers no rights.

[Miro]

"Gerald" wrote:

> Is there a way to force the Date formats in the report to use dd/mm/yyyy or
> use the Name of the Month/dd/YYYY in the DEX.ini?
>

> Hi Mariano,
I was just looking over your advice on changing the date format, and I need
to change mine to print the month in an abbreviated form on our cheques. (eg
04/02/10 to show as 04 Feb 2010) but I can not figure out what the format
sting is for this display. Any help would be greatly appreciated
Miro

[David Musgrave [MSFT]]

Hi Miro

I suggest you use the RW_DateToString() user defined function (system series) in a calculated field.

The parameters are:

in date IN_Date;
in string IN_Format;
{
Format Syntax:-
ddd = day of week as 3 letters
dddd = day of week in full
N = day of year
NN = day of year (padded)
D = day of month
DD = day of month (padded)
M = month of year
MM = month of year (padded)
mmm = month of year as 3 letters
mmmm = month of year in full
YY = year of date (2 digits)
YYYY = year of date (4 digits)
}

So for 04 Feb 2010, use "DD mmm YYYY" as your format string.

David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific

Microsoft Dynamics (formerly Microsoft Business Solutions)
http://www.microsoft.com/Dynamics

mailto:David dot Musgrave at microsoft dot com

[Miro]

Thank you for your help

[David Musgrave [MSFT]]

My pleasure, glad to be of assistance.