determining week# of fiscal period?

D

djeans

Hello,

I am trying to figure out how to determine the week number of th
current period based on a given date.

I found a thread on here about determining period number that ha
worked well:

=MONTH(EOMONTH(A1,0))

Which returns the current period (7 - our periods start in January
but, I would like a formula that would return "Period 7 - Week 4" fo
instance.

Any help would be greatly appreciated.

Darron Jean
 
D

djeans

I look through that page, but I didn't see anything that related to m
problem. (or maybe it did, but it was over my head).

It seemed to deal with just determing the week of the current year.

Darro
 
D

djeans

After reading my original post, I am afraid it might be hard t
understand what I am talking about.

I would like to know how far into the period am am each week. Fo
example, some periods have 5 weeks, most have four. I am trying t
create a formula that would tell me that I am currently in Week 4 o
Period 5.

Thanks again for any help.

Darro
 
N

Norman Harker

Hi Darron!

You seem to have some monthly based financial period scheme. But
before we can help, we really need you to define the algorithm.

eg
Period 1 starts the first Monday of January
Period 2 starts the first Monday of February
.....

If that is the algorithm then I think it's a trip to:

Chip Pearson:
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoW
 
D

djeans

Tried the VBA function, but couldn't get it to compile, although, I'
not sure if it would have returned what i needed.

This has turned out to be tougher to describe than I thought it woul
be. Now that I think about it, this may not be doable in excel. Let m
know if whit I am trying to do is impossible.

Let me try to explain more fully.

My company's year always has 52 weeks, that always start on Monday
2004 began on 12/29/03, and the fiscal year 2005 will begin o
12/27/04.

I would like to have a formula that, based on a reference date, woul
return the period number and week number of that period.

For instance, for the reference date 7/19/04, I would like it to retur
"Period 7 - Week 4"

As a bonus, if I could have another formula that could tell if th
period has 4 or 5 weeks would be great.

thanks

darro
 
Top