Is there a function in Acumatica similar to C# where I can return the total number of days in a month for a GI Column?
For example, I want to return the number of days for May of 2018. Using C# I can use the statement below to do that. Is there a function in Acumatica that I can do the same in a GI?
This function returns the value of 31.
int days = DateTime.DaysInMonth(2018,05);
Would also be great if I could also determine the weeks in a month returned as an INT. But I really need the days.
Best answer by miguel80
Hi,
You can use this fórmula:
=Day(DateAdd('12/31/2022','m',Month(Today())))
Replacing the Today() with the date you want to evaluate. You should send the first date as the last day of the previous year so that it also works on leap years, so it would look like this:
=Day(DateAdd(Concat('12/31','/',CStr(Year(Today())-1)),'m',Month(Today())))
It’s a little too much perhaps but it will get the job done.
+13You could use DateDiff(‘d’, date1, date2) to find the difference (in days) between two dates. If you could somehow pass the month start and month end into the formula, it should calculate it. I’m not sure if you could use the built-in parameters @MonthStart and @MonthEnd.
+1Hi,
You can use this fórmula:
=Day(DateAdd('12/31/2022','m',Month(Today())))
Replacing the Today() with the date you want to evaluate. You should send the first date as the last day of the previous year so that it also works on leap years, so it would look like this:
=Day(DateAdd(Concat('12/31','/',CStr(Year(Today())-1)),'m',Month(Today())))
It’s a little too much perhaps but it will get the job done.
I’ll prefix that this might be overkill:
There is the DateInfo table and it might be easier to extend it and add a column for the number of days in the month on each record to let you do a single join for easier reporting formulas.
+1Better answer than my last one:
Join to the FinPeriod Table it has a “Length” field and it contains the length in days of each period. As long as your periods match the months you can use that.
I only need to figure out how to link SOLine to FinPeriod then.
Will crack on with that and see if it works.
+1Use the date you want to evaluate. It has to be “Greater or equal than” FinPeriod.StartDate and “Less than” (without the equal) FinPeriod.EndDate
= DateDiff( 'd' , @MonthStart, @MonthEnd)
+1Use the Today() funtion to join to the FinPeriod table. Then you can use the EndDate or the value. If it’s duplicating te lines let me know, there´s another field you have to filter by but I don’t recall it right now.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
