Skip to main content
Answer

Function to return Week #/ WeekID for a given date in Import Scenarios

  • September 1, 2024
  • 4 replies
  • 178 views
ebeany
Freshman III

Is there a function that can be used in Import Scenarios to return the ISO 8601 Week Number (equivalent to Excel’s ISOWEEKNUM function) for any given date in the import file - or better still, the Acumatica WeekID , being the ISO week number in YYYYWW format for a given date?

I’m trying to create an import scenario to append lines to Equipment Time Cards, but the source data only has date but not week ID.

(Site is not using custom weeks).

I’d rather not have to write a complicated nest of DateAdd this, DayOfWeek that, Year this, DateDiff that if I can avoid it!

 

 

Best answer by ebeany

Here’s the formula I’ve now implemented on an import scenario for equipment time (Source in my case is Excel). This returns the ISO Year and week for a given date in the same yyyyww format as Acumatica. The same formula will also work in a Generic Inquiry. You’ll need to delete the comment lines, and replace all occurrences of [Date Entry] with the name of your date column.

-- ISO 8601 Week ID for Acumatica GI or Import Scenario
-- Returns the weekID in yyyymm format for a transaction date.
-- Replace all occurrences of [Date Entry] with the column name of your transaction date. 
-- Comment lines will need to be removed.
-- NB. DayOfWeek() function returns 1 for Sunday, 7 for Saturday

-- Scenario 1: If following calendar year starts on Tue (3), Wed (4) or Thu (5), then last up to 3 days of this calendar year may be week 1 of following year
=Iif(
		(
		 DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) >= 3 and 
		 DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) <= 5 and
		 DateDiff('d',CDate([Date Entry]),CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) +2 <= DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01'))
		)
	  , CStr(Year(CDate([Date Entry]))+1) +'01'
	  , 
-- Scenario 2: If this calendar year starts on Fri (6), Sat (7) or Sun (1), then first up to 3 days of this calendar year may be week 52 or 53 of last year
	    Iif(
				(CDate([Date Entry]) = CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01') and DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) = 1)
			  OR
				(
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) >= 6 and
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) <= 7 and
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) + DateDiff('d',CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'),CDate([Date Entry])) <= 8	
				)
			, CStr(Year(CDate([Date Entry]))-1)
				 + PadLeft(CStr(datediff(
								  'w'
								 ,
								 DateAdd(
											CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01')
											,'d'
											,Iif(DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01')) <= 5
												,2 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01'))
												,9 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01'))
											   )
										)
								 , 
								  CDate([Date Entry])

								)+1 
							),2,'0')
-- Scenario 3: Failing Scenario 1 or 2, it's a week in the same year.
			, CStr(Year(CDate([Date Entry]))+0)
				 + PadLeft(CStr(datediff(
								  'w'
								 ,
								 DateAdd(
											CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')
											,'d'
											,Iif(DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) <= 5
												,2 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'))
												,9 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'))
											   )
										)
								 ,
								  CDate([Date Entry])

								)+1 
							),2,'0')
			)			
)

 

4 replies

WillH
Varsity III
Forum|alt.badge.img+3
  • WillH
  • Varsity III
  • September 3, 2024

What are you being fed in from your time card import? (What format are dates being supplied in as)

 

YYYYMMDD?

YYYY-MM-DD?

YYYY-MM-D?
(the list continues endlessly, I’m hoping you’re being fed a simple ISO date format, but your information above isn’t specific.)

 

Bonus question to remove assumptions:
What data format are you being fed?  CSV? Tab Delimited?
If Excel I’d just advise to use the excel function, so I’m guessing it’s not that simple.​​​

~Will H (MYOB)
Chris Hackett
ebeany
Freshman III
  • ebeany
  • Author
  • Freshman III
  • September 3, 2024

Hi @WillH , the provider is Excel, so, Excel Datetime, but the Excel file is being produced by another source. Getting the other source to do it isn’t an option, and we don’t want them to have to manipulate each Excel file, add an ISOWEEKNUM column, and then copy and paste as values, before importing. 

I mean, if I have to write a nice long nested IFF function, I will (I’m already partway there) but… it’s not pretty to read. If only the Import Scenarios would let us use SQL  ;-/

Assuming there isn’t a function I can use in the Import Scenario, I’ll post my formula here once I’ve finally nailed it.

 

 

WillH
Melissalutrick21
Freshman II
Forum|alt.badge.img

I have a manufacturing customer that wants to use Week numbers in their serial numbering but I have been unable to find Weeks within Acumatica’s numbering. We were headed down the custom route. 

MdL
Chris Hackett
ebeany
Freshman III
  • ebeany
  • Author
  • Freshman III
  • Answer
  • September 6, 2024

Here’s the formula I’ve now implemented on an import scenario for equipment time (Source in my case is Excel). This returns the ISO Year and week for a given date in the same yyyyww format as Acumatica. The same formula will also work in a Generic Inquiry. You’ll need to delete the comment lines, and replace all occurrences of [Date Entry] with the name of your date column.

-- ISO 8601 Week ID for Acumatica GI or Import Scenario
-- Returns the weekID in yyyymm format for a transaction date.
-- Replace all occurrences of [Date Entry] with the column name of your transaction date. 
-- Comment lines will need to be removed.
-- NB. DayOfWeek() function returns 1 for Sunday, 7 for Saturday

-- Scenario 1: If following calendar year starts on Tue (3), Wed (4) or Thu (5), then last up to 3 days of this calendar year may be week 1 of following year
=Iif(
		(
		 DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) >= 3 and 
		 DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) <= 5 and
		 DateDiff('d',CDate([Date Entry]),CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01')) +2 <= DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+1)+'-01-01'))
		)
	  , CStr(Year(CDate([Date Entry]))+1) +'01'
	  , 
-- Scenario 2: If this calendar year starts on Fri (6), Sat (7) or Sun (1), then first up to 3 days of this calendar year may be week 52 or 53 of last year
	    Iif(
				(CDate([Date Entry]) = CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01') and DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) = 1)
			  OR
				(
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) >= 6 and
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) <= 7 and
				  DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) + DateDiff('d',CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'),CDate([Date Entry])) <= 8	
				)
			, CStr(Year(CDate([Date Entry]))-1)
				 + PadLeft(CStr(datediff(
								  'w'
								 ,
								 DateAdd(
											CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01')
											,'d'
											,Iif(DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01')) <= 5
												,2 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01'))
												,9 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))-1)+'-01-01'))
											   )
										)
								 , 
								  CDate([Date Entry])

								)+1 
							),2,'0')
-- Scenario 3: Failing Scenario 1 or 2, it's a week in the same year.
			, CStr(Year(CDate([Date Entry]))+0)
				 + PadLeft(CStr(datediff(
								  'w'
								 ,
								 DateAdd(
											CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')
											,'d'
											,Iif(DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01')) <= 5
												,2 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'))
												,9 - DayOfWeek(CDate(CStr(Year(CDate([Date Entry]))+0)+'-01-01'))
											   )
										)
								 ,
								  CDate([Date Entry])

								)+1 
							),2,'0')
			)			
)

 

craig2
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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