Date conversion..Month Start, Month End Date

[pra_kale]

Hi,

I want to schedule the report for a MTD extraction. Means it will extract the data like 1 sep to 19 sep, 1 To 20 Sep and  on 1 Oct 2016 it will extract the report for complete month i.e. 1 to 30 sep. and again from 2 nd oct onwards  it will extract the report 1 to 2nd oct, 1 to 3 rd oct. and so on.

Same way in another report i want to schedule a report on the basis of Financial Month. Means up to 1 oct. Month will be 6 i.e. September and from 2nd Oct. till 1 Nov Month will become 7.

I have created a below given variables...currently this variables are giving correct dates but not sure whether in next month i.e. in 1st Oct it will holds good.

Please suggest..

To find Month start date....=date(if(Today()-1>=MonthStart(Today()-1),MonthStart(Today()-1)))

To Find MTD date.....=date(if(Today()-1<=MonthEnd(Today()-1),(Today()-1)))

To Find FINMONTH...=if(num(month(Today()))<=3,num(Month(Today()))+9,num(Month(Today()))-3)

Thanks in Advance.

[sunny_talwar]

Since today is 20th, you want 1-20 Sep, right?

Try this for MTD

=Date(If(Day(Today()) = 1, Today() - 1, Today()))

MonthStart could be this:

=Date(MonthStart(If(Day(Today()) = 1, Today() - 1, Today())))

[pra_kale]

Hi,

Thanks sunny..Actually for MTD i want data up to 19th so i have modified the formula

=Date(If(Day(Today()) = 1, Today() - 1, Today())-1). Whether it is right..

Secondly, by keeping same formulas of MTD and Month start can I get Data for 1 sep to 30 sep complete month data on 1 oct. and again from 2nd oct i want data for 1 st oct and on 3rd oct for period 1 to 2nd oct and so on..

[sunny_talwar]

What about 2nd Sept? The date needs to be 1 or 2?

[pra_kale]

On 2nd oct. i will require a data of 1 oct..on 3 Oct I require a data frm 1 to 2 nd oct and so on..and on 1 st Nov i will require a data for 1 to 31 oct..means always up to the Month data.

[sunny_talwar]

Then why not just use Date(Today() - 1) for MTD?

[pra_kale]

You are right Sunny..this will do.

i forgot such simple thing..

just one more thing in one report i am going to use below given formula to calculate MTD data same way but on Financial Month..As below given formula currently giving me month as 6 but on 1 st oct it will give me month as 7. Where as on 1st i will require clmplete data for 6 i.e sep. and from 2 nd onwsrds for 7 month 7 i.e Oct.

To Find FINMONTH...=if(num(month(Today()))<=3,num(Month(Today()))+9,num(Month(Today()))-3)

[sunny_talwar · Accepted solution]

May be this:

=If(Month(Today() - 1) <= 3, Month(Today() - 1) + 9, Month(Today() - 1) - 3)

[pra_kale]

Thanks Sunny..It is working.