Year(Today()) is not giving correct value in Script Editor

[vishnumusani]

Hi All,

I am deriving a date based upon a year value by keeping month as '12' and date as '31' so that my date will appear as YYYY/12/31.

For current year i need to create date as "CurrentYear/Current Month/lastdayofpreviousmonth"

Year     Date

2009     2009/12/31

2010     2010/12/31

2015     2015/10/31 (As previous month is October)

In order to achieve that i am writing below code. but Year(Today()) is always giving '1905-07-07' because of that my formula is not working, Can you please suggest.

if( (year(Today()) =[Calendar Year]),Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

[MK_QSL]

IF(Year(Today()) = InvoiceYear, Date(MonthStart(Today())-1,'DD/MM/YYYY'), Date(MakeDate(InvoiceYear,12,31),'DD/MM/YYYY'))

Change InvoiceYear to [Calendar Year]

[thakkarrahul01]

Hi Vishnu,

Can you try specifying format for today :

date(today(),'YYYY/MM/DD')

I think today function is giving you default format the way you have set at start while loading the model. Please tell me if it helps.

Kind Regards,

Rahul

[Anonymous]

Syntax is wrong, bracket after ')' will not come here, it will come at the end before 'as'

if( (year(Today()) =[Calendar Year]),Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

Try this:

if( (year(Today()) =[Calendar Year],Date((MonthStart(Today())-1),'YYYY-MM-DD'), date#(date(makedate([Calendar Year],'12','31'),'YYYY-MM-DD'),'YYYY-MM-DD')) as Tran_Date;

[swuehl]

LOAD Year,

          Date( if(Year = Year(today()), Monthstart(today())-1, makedate(Year,12,31)),'YYYY-MM-DD') asTran_Date;

LOAD * INLINE [

Year

2009

2010

2015

];

[tamilarasu]

Hi Vishnu,

Try,

If( Year(Today()) =[Calendar Year],

          Date(MonthStart(Today())-1),'YYYY-MM-DD'),

          Date(MakeDate([Calendar Year],'12','31'),'YYYY-MM-DD') ) As Tran_Date;

[vishnumusani]

HI All,

Thanks for your response.

First of all Year(Today()) is not giving 2015, it is giving  "1905-07-07" . that's the reason all our formulas won't work.

Hope you got the issue i am facing.

Regards,

Vishnu

[swuehl]

Year(Today()) is returning 2015. You can easily check this in a text box

=Year(Today() )

Only if you format that number (i.e. 2015) as Date, i,e,

=Date(Year(Today() ) )

then you will get something like 1905-07-07, which has a numeric representation of 2015.