How to display start date as start DATE OF month?

[#1]

Hi team ,

I have a date column and fiscal year column like below

Year and Month

2011/01 (Jan)

2011/02 (Feb)

2012/03(Mar)

Now i want to display like below

2011-1-1

2012-2-1

2012-3-1

thanks

[#2]

=Date(Date#([Year and Month], 'YYYY/MM'), 'YYYY-M-D')

Cheers

[#3]

Hi, Martin

I TRIED BUT ITS NOT WORKING,

[kaushiknsolanki]

Try this.

TestData:

LOAD if(Len(Date) = 13, Date(Date#(Date,'YYYY/DD (MMM)')),Date(Date#(Date,'YYYY/DD(MMM)'))) as New_Date,Date INLINE [
Date
2011/01 (Jan)
2011/02 (Feb)
2012/03(Mar)
];

Regards,

Kaushik Solanki

[#5]

Hi,

its not working

Date

2011-02-23

2011-02-25

2011-03-01

2011-05-30

Output like below

2011-02-01

2011-02-01

2011-03-01

2011-05-01

please help

thanlks

[kaushiknsolanki]

Hi,

     But in the example you gave, dates are not given.

2011/01 (Jan)

2011/02 (Feb)

2012/03(Mar)

Regards,

Kaushik Solanki

[MayilVahanan]

HI

Try like this

=MonthStart(Date(Date#('2011-02','YYYY-MM'),'YYYY/MM'))

or

=MonthStart(Date(Date#('2011-02-23','YYYY-MM-DD'),'YYYY/MM/DD'))

its for text box.

in script, simply use monthstart() function for ur date, if its in correct date format,other wise use date#() function.

hope it helps

[#8]

HI

TRY THIS

=date(date#('2011-02-23','YYYY-MM-DD'),'YYYY-MM-01')

or

=date(date#(datefield,'YYYY-MM-DD'),'YYYY-MM-01')

hope this helps you