topic Wow.. that was brilliant... in Product Forum

Select Previous month within datetimes

SébastienBulté — Thu, 08 Jun 2017 13:35:00 GMT

Hello! I was wondering if anyone has a good idea on how I should perform following business logic: Create 2 DateTime strings , one is 16th of previous month and the other is the end of the previous month (28th or 30th or 31th day); I don't know however how I can state that the datetime should point its day to the last day of that month, is there any function for this? The current DateTime string I have is: "16-"&FormatDate(AddMonths(Today(); -1); "MM")&"-"&FormatDate(Today(); "yyyy") I however want to know how I can make the end of the month dynamic, aka. I don't want to hardcode the 31 in following expression: "31-"&FormatDate(AddMonths(Today(); -1); "MM")&"-"&FormatDate(Today(); "yyyy") Thanks! SÃ©ba

Thanks for the hint. Solution

SébastienBulté — Thu, 08 Jun 2017 15:52:00 GMT

Thanks for the hint. Solution for reference: FormatDate(AddDays(MakeDate(1; FormatDate(Today(),""MM""); FormatDate(Today(),""yyyy"")),-1),""dd"")&""-""&FormatDate(AddMonths(Today(); -1); ""MM"")&""-""&FormatDate(Today(); ""yyyy"") There should be better date manipulation tools by BP than this no?

One other minor comment /

hari.murali — Thu, 08 Jun 2017 20:04:00 GMT

One other minor comment / consideration is finding the last day. There should be a choice stage to identify the end day (28 or 29 or 30 or 31). For 31: Expression: ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 1 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 3 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 5 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 7 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 8 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 10 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 12 True: MakeDate(31, ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")), FormatDate(Today(), ""yyyy"") For 30: Expression: ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 4 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 6 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 9 OR ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 11 True: MakeDate(30, ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")), FormatDate(Today(), ""yyyy"") For 28: Expression: ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 2 AND InStr(ToNumber(FormatDate(Today(), ""yyyy"")) / 4, ""."")0 True: MakeDate(28, ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")), FormatDate(Today(), ""yyyy"") For 29: Expression: ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")) = 2 AND InStr(ToNumber(FormatDate(Today(), ""yyyy"")) / 4, ""."")=0 True: MakeDate(29, ToNumber(FormatDate(AddMonths(Today(), -1), ""MM"")), FormatDate(Today(), ""yyyy"") I have built an utility (Make Date) to perform these type of actions.

You can easily use AddDays

Christer_Tellefsen — Fri, 09 Jun 2017 16:16:00 GMT

You can easily use AddDays(FormatDate(Today(); ""01.MM.yyyy""); -1) First we formate the date to 01.MM.yyyy then we add -1 day back. Today() = 09.06.2017 Result: 31.05.2017

Or

Christer_Tellefsen — Fri, 09 Jun 2017 16:32:00 GMT

Or AddDays(AddMonths(FormatDate(Today(); ""01.MM.yyyy""); [Months]); -1) To get more end of the month 🙂

Wow.. that was brilliant...

hari.murali — Fri, 09 Jun 2017 22:20:00 GMT

Wow.. that was brilliant... Thanks Christer.

Ah I wasn't aware we could

SébastienBulté — Mon, 12 Jun 2017 02:16:00 GMT

Ah I wasn't aware we could hardcode date values into the format string, thanks. This at least simplifies things a bit.