determining week# of fiscal period?

[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

 

[JE McGimpsey]

Take a look at

http://cpearson.com/excel/weeknum.htm

 

[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

 

[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

 

[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

 

[mg]

Mon, 19 Jul 2004 20:47:40 -0500, djeans wrote :

Hello,

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

I found a thread on here about determining period number that has
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" for
instance.

Any help would be greatly appreciated.

Darron Jeans

I'm not sure if i understand you correctly but maybe this VBA function
may help

Option Explicit

Function DayOfPeriod(TempDay As Date, Period As String) As String

'Period can be "w" -week , "m" -month , "q" - 1/4 year , "t" - 1/3
year , "h" - 1/2 year, "y" - year

Dim NameOfPeriod As String
Dim Diw As Integer
'----
Dim NrOfPeriod As Integer
Dim NrOfDay As Integer
'----
Select Case Period

Case "y"

NameOfPeriod = "year"
NrOfPeriod = Year(TempDay)
NrOfDay = DateDiff("d", DateSerial(Year(TempDay), 1, 1), TempDay) + 1

Case "w"

NameOfPeriod = "week"
NrOfPeriod = DateDiff("ww", DateSerial(Year(TempDay), 1, 1), TempDay)
+ 1
NrOfDay = WeekDay(TempDay, vbMonday)

Case "m"

NameOfPeriod = "month"
NrOfPeriod = Month(TempDay)
NrOfDay = Day(TempDay)

Case "q", "t", "h"
Select Case Period
Case "q"
NameOfPeriod = "1/4 yr"
Diw = 12 / 4
Case "t"
NameOfPeriod = "1/3 yr"
Diw = 12 / 3
Case "h"
NameOfPeriod = "1/2 yr"
Diw = 12 / 2
End Select

NrOfPeriod = ((Month(TempDay) - 1) \ Diw) + 1
NrOfDay = DateDiff("d", DateSerial(Year(TempDay), Diw * (NrOfPeriod -
1) + 1, 1), TempDay) + 1

Else


End Select

DayOfPeriod = NrOfPeriod & " " & NameOfPeriod & " , " & NrOfDay & "
day"
End Function

 

[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