How to create Start Date and End Date

[jim_chan]

Hi Guys,

I have a Month field - Jan until Dec. Now i need to create a Start Date and End Date field, but is empty at the moment.

but i want to achieve here is for example: if is Month Jan - Start Date is 1/1/2016 and End Date is 31/01/2016.

How can i achieve that?

Rgds,

Jim

[MK_QSL]

=MonthStart(MakeDate(Year(Today()),Month(Date#('Jan','MMM'))))

=MonthEnd(MakeDate(Year(Today()),Month(Date#('Dec','MMM'))))

[anagharao]

Hi ,

You could use MonthStart and MonthEnd functions

Example:

=MonthStart(DATE#(Month(TODAY())&Year(TODAY()),'MMMYYYY'))

In the above example you would have to use Month and Year field instead of 'Month(TODAY())' and 'Year(TODAY())'.

[jim_chan]

Hi there,

i came across this.. why not use "MakeDate()" ?

Rgds

Jim

[jim_chan]

Hi there,

i came across this.. why not use "MakeDate()" ?

Rgds

Jim

[jim_chan]

HI there,

Why not just create as such? :

makedate(Year, Month, 1) as StartDate,

MonthEnd(StartDate) as EndDate

[MK_QSL · Accepted solution]

1) If you have Month not in Dual form, first you have to interpret it to DUAL format using Date# function.

2) you have mentioned that you have Month field only. I assumed that you don't have date and year fields.

    If you have Year field, you can use as below.

=MonthStart(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

=MonthEnd(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

3) If you want to use

     MonthEnd(StartDate) as EndDate, you need to use Preceding Load.

Provide your exact requirements with some sample data.

[anagharao]

yup,

You could do that as well.

[tresesco]

You CAN use makedate() here, provided your Month field is numeric. If that is not the case, you could still use it like:

MakeDate(Year, Month(Date#(Month, 'MMM')), 1)

Here, Month(Date#(Month, 'MMM')) - converts your text months into numbers.

[jim_chan]

thank you master. thank you!