Hi All,
I want to add a condition to a generic inquiry to filter timecards created on last week. To do that I think that I would need to compare EPTimeCard.WeekID with current WeekId. Is there a way to populate Value field of GI with current WeekID ?
Thanks
Best answer by Naveen Boga
View originalHi
Corrected. Your mileage may vary.
=Year(DateAdd(Today(),'d',-6-DayOfWeek(Today()))) * 100 +
Floor( DateDiff('d',
DateAdd(CDate('1/1/'+Year(DateAdd(Today(),'d',-6-DayOfWeek(Today())))),'d',7 *
Round(DayOfWeek(CDate('1/1/'+Year(DateAdd(Today(),'d',-6-DayOfWeek(Today())))))/7,0)
)
,
DateAdd(Today(),'d',-6-DayOfWeek(Today()))
) / 7 )
+ 1
Here is a better way to calculate WeekID in Acumatica. The end of the year gives the wrong number using the first method.
=DateAdd(Year(DateAdd(Today(),'d',-6-DayOfWeek(Today()))) * 100 ) +
Floor(
DateDiff('d',
Round(
DayOfWeek(CDate('1/1/'+DayOfYear(DateAdd(Today(),'d',-6-DayOfWeek(Today())))))/7,0
),
DateAdd(Today(),'d',-6-DayOfWeek(Today()))
) / 7
)
+ 1
+1
This is how I’ve done it in GI’s for This or Last weeks Timecards.
=IIf([AllWeeks] = 'Last' , [LastWeekStart], iif([AllWeeks] = 'Current' ,[ThisWeekStart],'' ))
This formula will generate a WeekID. It uses DateAdd() and DayOfWeek() to get last week’s Monday, then uses that Monday to calculate the week number.
The formula cannot be used as a default value in a GI parameter, however it CAN be used as a GI condition or the default value in a report parameter.
Excel VBA
=Year(DateAdd("d", -6 - Weekday(Date, 2), Date)) * 100 + Application.WorksheetFunction.Floor(DateDiff("d", CDate("1/1/" & Year(DateAdd("d", -6 - Weekday(Date, 2), Date))), DateAdd("d", -6 - Weekday(Date, 2), Date)) / 7) + 1Acumatica
=Year(DateAdd(Today(),'d', -6 - DayOfWeek(Today()))) * 100 + Floor(DayOfYear(DateAdd(Today(),'d', -6 - DayOfWeek(Today()))) / 7) + 1
+17
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.