PC Review


Reply
Thread Tools Rate Thread

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

 
Reply With Quote
 
 
 
 
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.

 
Reply With Quote
 
 
 
 
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

 
Reply With Quote
 
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

 
Reply With Quote
 
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/
>



 
Reply With Quote
 
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
 
Reply With Quote
 
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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

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 Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
To automatically give fiscal period/fiscal year Turnipboy Microsoft Excel Discussion 7 19th Jan 2006 10:14 PM
Function: Two Month Date Spans From Fiscal Period - an example DataFreakFromUtah Microsoft Excel Programming 1 13th May 2004 03:32 AM
display quarter and fiscal year D S K Microsoft Excel Programming 1 7th May 2004 03:44 PM
Excel 2000 - VBA and fiscal periods Matt. Microsoft Excel Programming 2 30th Sep 2003 08:47 PM
creating a calendar that covers a fiscal year BBromen Microsoft Outlook 0 4th Aug 2003 01:25 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:03 PM.