Previous month date-time new definition

John-S Pretorius
John-S Pretorius used Ask the Experts™
on
I am trying to create a new definition for 'previous month' from a dropdown parameter selection. The typical solution = 'LastFullMonth' but I need it to be from the first day of the previous month 03:00:00am to the first of the current month 02:59:59am
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm not sure how the "from a dropdown parameter selection" part fits in, but here's one way to get the range that you're looking for in formulas.

 LastFullMonth produces a range of dates, so you can use Minimum and Maximum to get the beginning and ending dates in the range, and then adjust those.

// 3 AM on the first day of the previous month
DateTime (Minimum (LastFullMonth), Time (3, 0, 0))

// 2:59:59 AM on the first day of the current month
DateTime (Maximum (LastFullMonth) + 1, Time (2, 59, 59))

 I add 1 to the date for the end of the last month, to get the first day of the current month.


 So you could, for example, use something like the following in a record selection formula:

{datetime field} >= DateTime (Minimum (LastFullMonth), Time (3, 0, 0)) and
{datetime field} <= DateTime (Maximum (LastFullMonth) + 1, Time (2, 59, 59))


 You could also use CurrentDate to get that range, but using LastFullMonth is probably the simplest solution.

 James
John-S PretoriusSystems Engineer

Author

Commented:
Thank you James
You're welcome.  Glad I could help.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial