determining week# of fiscal period?

Discussion in 'Microsoft Excel Discussion' started by djeans, Jul 20, 2004.

  1. djeans

    djeans Guest

    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
     
    djeans, Jul 20, 2004
    #1
    1. Advertisements

  2. djeans

    JE McGimpsey Guest

    Take a look at

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

    In article <>,
    djeans <<>> 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.
     
    JE McGimpsey, Jul 20, 2004
    #2
    1. Advertisements

  3. djeans

    djeans Guest

    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, Jul 20, 2004
    #3
  4. djeans

    djeans Guest

    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
     
    djeans, Jul 20, 2004
    #4
  5. 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

    "djeans >" <<> wrote in message
    news:...
    > 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/
    >
     
    Norman Harker, Jul 20, 2004
    #5
  6. djeans

    mg Guest

    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
     
    mg, Jul 20, 2004
    #6
  7. djeans

    djeans Guest

    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
     
    djeans, Jul 20, 2004
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jlyons360

    I need to get week start and week end dates to change automatically

    jlyons360, Jul 8, 2004, in forum: Microsoft Excel Discussion
    Replies:
    2
    Views:
    300
    Trevor Shuttleworth
    Jul 8, 2004
  2. Replies:
    2
    Views:
    214
  3. Maddoktor

    Add start of new week date when new week starts

    Maddoktor, Jun 3, 2005, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    205
    JE McGimpsey
    Jun 3, 2005
  4. How do i calculate fiscal week of the year

    , Sep 21, 2005, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    670
  5. Turnipboy

    To automatically give fiscal period/fiscal year

    Turnipboy, Jan 18, 2006, in forum: Microsoft Excel Discussion
    Replies:
    7
    Views:
    398
    Roger Govier
    Jan 19, 2006
Loading...

Share This Page