formatting dates

[#1]

Hi, I have an If statement in my Dimension Expresion box to limit my date dimension to 18 months.

=Date(If(PeriodDate > MonthStart(Today(),-18), PeriodDate, Null()), 'MMM/YYY')

It works in limiting my chart to 18 months but now the dates show as numbers ( 40600 etc ).  How can I get these to show as dates.  The load statements loads the dates as MM/YYYY.    Thanks, Jim

[MayilVahanan]

Hi

Try like this

=Date(If(PeriodDate > MonthStart(Today(),-18), PeriodDate), 'MM/YYYY')

Hope it helps

[Nicole-Smith]

Try:

=If(PeriodDate > MonthStart(Today(),-18), Date(PeriodDate, 'MMM/YYY'), Null())

[#4]

still get the number. thanks for trying

[#5]

still getting the number....

[MayilVahanan]

Hi

     What is the PeriodDate format? is date ? or number? or text?

     Try this , if its text format..Can you post a sample file

     Date#(PeriodDate,'MM/YYYYY')

[#7]

It's stored in my MS SQL table as 'smalldatetime'.  When I load into QV I set date format as MM/YY

[MayilVahanan]

Hi

=Date(If(PeriodDate > Date(MonthStart(Today(),-18),'MM/YY'), PeriodDate), 'MM/YYYY')

What gives for this:

if(PeriodDate > MonthStart(Today(),-18), PeriodDate) number or date..if date means what format?

[muniyandi · Accepted solution]

Hi,

francis your date format as MM/YY Means (you need monthwise result ? )

Set Variable FirstDT, = Date#(DATE(MONTHSTART(TODAY(),-18),'MM/YY')))

Using Dimension expression like:

=IF (Date#(DATE(PeriodDate,'MM/YY')) >Date#(DATE(MONTHSTART(TODAY(),-18),'MM/YY')),Date#(DATE(PeriodDate,'MM/YY')),NULL())

Or

using Variable

=IF (Date#(DATE(PeriodDate,'MM/YY')) >FirstDT,Date#(DATE(PeriodDate,'MM/YY')),NULL())

Hops it will help

Muni

[Anonymous]

Looks right.  See number formatting on the front end, in document properties, maybe in object (list box) properties.