NewDateFormatter Use

Post Reply
MarenC
Posts: 108
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

NewDateFormatter Use

Post by MarenC » Thu Jul 30, 2020 10:31 am

Hi,

I want to get the serial date of the following date:

31/07/2020.

Normally I would subst this and use DayNo.

But I thought I would try the following:

Code: Select all

cDate = '31/07/2020';

vDateFormater = NewDateFormatter('');
vDateCompareSerial = ParseDate(vDate, 'dd/mm/yyyy');
vDateCompare = FormatDate( vDateCompareSerial, 'yyyy-mm-dd' );
I get the following results when I output to excel:

vDateCompareSerial = 21945.004861111
vDateCompare = 31/07/2020

But vDateCompareSerial equates to 31/01/2020 and not 31/07/2020.

What is going on?

Maren

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: NewDateFormatter Use

Post by Wim Gielis » Thu Jul 30, 2020 11:43 am

Did you check the documentation on the date patterns ?
http://userguide.icu-project.org/formatparse/datetime

I would use 'dd/LL/y' and 'dd LL y' for your patterns.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

MarenC
Posts: 108
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: NewDateFormatter Use

Post by MarenC » Thu Jul 30, 2020 12:18 pm

Hi Wim,

I didn't know I did need to check those patterns so thanks for that.

I see the issue now, the small m was presuming minute.

If I change it as follows it appears to work:

Code: Select all

cDate = '31/07/2020';

vDateFormater = NewDateFormatter('');
vDateCompareSerial = ParseDate(vDate, 'dd/MM/yyyy');
vDateCompare = FormatDate( vDateCompareSerial, 'dd/MM/yyyy' );
Your patterns work too but I wanted the patterns to resemble the vDate and M is more familiar than L for months

thanks for your assistance

Maren

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: NewDateFormatter Use

Post by Wim Gielis » Thu Jul 30, 2020 12:35 pm

MarenC wrote:
Thu Jul 30, 2020 12:18 pm
Hi Wim,

I didn't know I did need to check those patterns so thanks for that.

I see the issue now, the small m was presuming minute.

If I change it as follows it appears to work:

Code: Select all

cDate = '31/07/2020';

vDateFormater = NewDateFormatter('');
vDateCompareSerial = ParseDate(vDate, 'dd/MM/yyyy');
vDateCompare = FormatDate( vDateCompareSerial, 'dd/MM/yyyy' );
Your patterns work too but I wanted the patterns to resemble the vDate and M is more familiar than L for months

thanks for your assistance

Maren
Hi,

indeed MM is an option too.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Post Reply