determining week# of fiscal period?

  • Thread starter Thread starter djeans
  • Start date Start date
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
 
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
 
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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top