NWA Intactix Users Group

For users of Intactix software in the NW Arkansas region and beyond
 
HomeCalendarGalleryFAQSearchRegisterMemberlistUsergroupsLog in

Share | 
 

 Function to change a date to a walmart week

View previous topic View next topic Go down 
AuthorMessage
mattharpo



Number of posts : 3
Registration date : 2007-01-15

PostSubject: Function to change a date to a walmart week   Thu Feb 11, 2010 10:53 am

Below is a function which you can use to input a date and return the Walmart week.

Just open the Visual Basic editor (under the developer tab on 2007, you may have to turn this on under Excel Options).
Go to Insert and then Module.
Then copy and paste the following code.

Code:
Function WMWeek(fordate As Date)
Application.Volatile (True)
WMWeek = (Int(IIf(DateSerial(Year(fordate), 2, 1) - fordate <= Weekday(DateSerial(Year(fordate), 2, 1)) And (DateSerial(Year(fordate), 2, 1) - fordate >= 0), 1, ((fordate - (IIf(Month(fordate) >= 2, DateSerial(Year(fordate), 2, 1), DateSerial(Year(fordate) - 1, 2, 1))) + Weekday((IIf(Month(fordate) >= 2, DateSerial(Year(fordate), 2, 1), DateSerial(Year(fordate) - 1, 2, 1))))) / 7) + 1)))
End Function

Now you can use this in your spreadsheet like any other function for example if you use
Code:
=WMWeek(Today())
it will return the Walmart week number for today's date

You can also do something like
Code:
=WMWeek(Today()-7)
to get last week's number

Or you can just input a date to get a week number like
Code:
=WMWeek(DATE(2010,2,20))
it would return "4"

Or point it to a cell with a date like
Code:
=WMWeek(A1)
Where A1="2/20/2010" it would return "4"

Hopefully you can find uses for this, it has come in very handy for spreadsheets where I want a week number to roll with with the current week or to convert a list of dates to their week numbers.
Back to top Go down
View user profile
Eric Liberatore
Admin
Admin
avatar

Number of posts : 489
Location : Bentonville, Akansas, USA
Company : Clorox
Title : Planogram Development Manager
Registration date : 2006-11-16

PostSubject: Re: Function to change a date to a walmart week   Tue Feb 16, 2010 2:45 pm

Very cool Matt! Thank you!
Back to top Go down
View user profile http://spaceuser.blogspot.com
 
Function to change a date to a walmart week
View previous topic View next topic Back to top 
Page 1 of 1
 Similar topics
-
» how to sort date in selenium
» Spring 2012 Fashion Week Sweepstakes *usa only*
» VALHALLA WOMENS WEEK VACATION GIVEAWAY
» How to parameterize with "today's date" with IDE
» Barometric Pressure

Permissions in this forum:You cannot reply to topics in this forum
NWA Intactix Users Group :: Tips N Tricks :: MS Excel-
Jump to: