Solved

Calculate or return the number of days in a month

  • 12 June 2023
  • 9 replies
  • 295 views
J
Rank
Userlevel 4
Badge

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.

icon

Best answer by miguel80 12 June 2023, 20:13

View original

9 replies

darylbowman
Rank
Badge +11

You 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.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
miguel80
Rank
Userlevel 5
Badge +1

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.

Miguel Q
D
Rank
Userlevel 7
Badge +5

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.

 

 

miguel80
Rank
Userlevel 5
Badge +1

Better 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.

Miguel Q
J
Rank
Userlevel 4
Badge

@miguel80 I like that!  I will try that...I think though I also have to pull the StartDate and EndDate columns, otherwise the Length displays as zero???

I only need to figure out how to link SOLine to FinPeriod then.

Will crack on with that and see if it works.

miguel80
Rank
Userlevel 5
Badge +1

Use the date you want to evaluate. It has to be “Greater or equal than” FinPeriod.StartDate and “Less than” (without the equal) FinPeriod.EndDate

Miguel Q
J
Rank
Userlevel 4
Badge

@miguel80 Well...that worked, but is not really what I needed.  I need to calculate (ie return) the number of days in the current month.  Trying to link the SOLine back to FinPeriod date just does not return the correct number of rows.

J
Rank
Userlevel 4
Badge

@darylbowman I have tried a couple of different ways to all @MonthStart and @MonthEnd inside of the DateDiff function of the GI Data Field.  Acumatica simply fails with an error that it cannot find it.  It lets you save that equation, but fails on execution. If Acumatica would let me do it that would be perfect.

= DateDiff( 'd' , @MonthStart, @MonthEnd)

 

miguel80
Rank
Userlevel 5
Badge +1

Use 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.

Miguel Q

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved