Skip to main content
Solved

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

  • September 1, 2024
  • 4 replies
  • 117 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')
			)			
)

 

View original
Did this topic help you find an answer to your question?

WillH
Varsity II
Forum|alt.badge.img+1
  • WillH
  • Varsity II
  • 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
1 person likes this
  • Chris Hackett
    Chris Hackett
ebeany
Freshman III
  • ebeany
  • 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
1 person likes this
  • WillH
    WillH
Melissalutrick21
Freshman II

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
1 person likes this
  • Chris Hackett
    Chris Hackett
ebeany
Freshman III
  • ebeany
  • Freshman III
  • 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
2 people like this
  • craig2
    craig2
  • Chris Hackett
    Chris Hackett

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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