4-5-4 Calendar date VBA and/or Add-In

[maruapo]

Hi:

Does anybody know of an Add-In that understands dates per the 4-5-4 fiscal Calendar? Doesn't have to be an Add-in, I suppose, if someone has figured out VBA language that does the same thing. There have got to be other people in the retail Financial community using the 4-5-4 calendar but can't get their Excel Macros to behave accordingly.

Every week and every month I run reports that sum up info from the previous fiscal week or fiscal month, but I'm always tweaking the results because, for example, week 1 of September 2005 in the Jovian calendar is not comprised of the same dates as week 1 of September 2005 in the 4-5-4 fiscal calendar. If such a thing exists, then all the Macros I run that reference "date" would understand that August 29, 2005 is actually week 1 of September 2005 in a 4-5-4 calendar. Boy, that would be a time-saver.

Thoughts? Suggestions?

maruapo

 

[Smitty]

We publish on a 5-4-4 calendar, so I just use a custom reference table:

Pub Dates.xls
	B	C	D	E	F	G	H	I
1	04Data	2004	05Data	2005	Weeks	Month	Quarter	Month #
2	40107	01/07/04	50105	01/05/05	5	January	Q1	1
3	40114	01/14/04	50112	01/12/05	5	January	Q1	1
4	40121	01/21/04	50119	01/19/05	5	January	Q1	1
5	40128	01/28/04	50126	01/26/05	5	January	Q1	1
6	40204	02/04/04	50202	02/02/05	5	January	Q1	1
7	40211	02/11/04	50209	02/09/05	4	February	Q1	2
8	40218	02/18/04	50216	02/16/05	4	February	Q1	2
9	40225	02/25/04	50223	02/23/05	4	February	Q1	2
10	40303	03/03/04	50302	03/02/05	4	February	Q1	2
11	40310	03/10/04	50309	03/09/05	4	March	Q1	3
12	40317	03/17/04	50316	03/16/05	4	March	Q1	3
13	40324	03/24/04	50323	03/23/05	4	March	Q1	3
14	40331	03/31/04	50330	03/30/05	4	March	Q1	3
Master

Hope that helps,

Smitty

 

[maruapo]

Thanks Smitty. This works great for now, but I'm still gonna hope someone can come up with an Add-In or some similar VBA that I can just plug into my Macros... Any takers???

 

[tusharm]

Not every solution has to be hard coded in VBA. If that worksheet works for you add it to your add-in and reference it in your code!

Thanks Smitty. This works great for now, but I'm still gonna hope someone can come up with an Add-In or some similar VBA that I can just plug into my Macros... Any takers???