Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
=MonthStart(MakeDate(Year(Today()),Month(Date#('Jan','MMM'))))
=MonthEnd(MakeDate(Year(Today()),Month(Date#('Dec','MMM'))))
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())'.
Hi there,
i came across this.. why not use "MakeDate()" ?
Rgds
Jim
Hi there,
i came across this.. why not use "MakeDate()" ?
Rgds
Jim
HI there,
Why not just create as such? :
makedate(Year, Month, 1) as StartDate,
MonthEnd(StartDate) as EndDate
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.
yup,
You could do that as well.
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.
thank you master. thank you!