# determining week# of fiscal period?

djeans
Guest
Posts: n/a

 20th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

JE McGimpsey
Guest
Posts: n/a

 20th Jul 2004
Take a look at

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

In article <(E-Mail Removed)>,
djeans <<(E-Mail Removed)>> wrote:

> 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.

djeans
Guest
Posts: n/a

 20th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

djeans
Guest
Posts: n/a

 20th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

Norman Harker
Guest
Posts: n/a

 20th Jul 2004
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

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(E-Mail Removed)
"djeans >" <<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> After reading my original post, I am afraid it might be hard to
> understand what I am talking about.
>
> I would like to know how far into the period am am each week. For
> example, some periods have 5 weeks, most have four. I am trying to
> create a formula that would tell me that I am currently in Week 4 of
> Period 5.
>
> Thanks again for any help.
>
> Darron
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

mg
Guest
Posts: n/a

 20th Jul 2004
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

--
Immortal under the Sun are the gods alone,
As for mortals their days must end -
What they achieve is but the wind!
Gilgamesh ,T2

djeans
Guest
Posts: n/a

 20th Jul 2004
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

--
Message posted from http://www.ExcelForum.com

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Turnipboy Microsoft Excel Discussion 7 19th Jan 2006 10:14 PM DataFreakFromUtah Microsoft Excel Programming 1 13th May 2004 03:32 AM D S K Microsoft Excel Programming 1 7th May 2004 03:44 PM Matt. Microsoft Excel Programming 2 30th Sep 2003 08:47 PM BBromen Microsoft Outlook 0 4th Aug 2003 01:25 AM

Features