Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Create Excel Worksheet Function

Hi Experts,

The company I work for uses a 4 week fiscal period and I would like to see if it would be possible to create a worksheet funtion that would retunr the fiscal period based on the datevalue entered.  Similar to the month function returning the number of the month of the datevalue.  The function would be =Period(datevalue)

The conditions and results for 2011 would look like this:

Between 12/27/10 and 01/23/11 ----->   01/11
Between 01/24/11 and 02/20/11 ----->   02/11
Between 02/21/11 and 03/20/11 ----->   03/11
Between 03/21/11 and 04/17/11 ----->   04/11
Between 04/18/11 and 05/15/11 ----->   05/11
Between 05/16/11 and 06/12/11 ----->   06/11
Between 06/13/11 and 07/10/11 ----->   07/11
Between 07/11/11 and 08/07/11 ----->   08/11
Between 08/08/11 and 09/04/11 ----->   09/11
Between 09/05/11 and 10/02/11 ----->   10/11
Between 10/03/11 and 10/30/11 ----->   11/11
Between 10/31/11 and 11/27/11 ----->   12/11
Between 11/28/11 and 12/25/11 ----->   13/11

The result would return as a string.

Any suggestions if this is possible and how it would be created?

Thanks

JJ
0
JJINFM
Asked:
JJINFM
1 Solution
 
Arno KosterCommented:
you could use the weeknumber formula for this :

=TEXT(1+FLOOR((B2-$B$1)/28,1), "0#") & "/" & TEXT(B2,"YY")

Open in new window


startdate 12/27/10 should be in cell B1 and the date for which the formula work then should be placed in cell B2.
You can drag the formula downwards to compute fiscal periods for cells B3, B4, B5 etc
0
 
barry houdiniCommented:
If you create a table with the start date of each period in one column and the period name for each(like 09/11) in the other then you can use a simple LOOKUP, e.g. with table in Y2:Z20 use this formu;a

=LOOKUP(A2,$Y$2:$Z$20)

where your date is in A2

otherwise, without a table you can use this formula

=TEXT(INT((A2-DATE(2010,12,27))/28)+1,"00")&"/11"

regards, barry
0
 
byundtCommented:
Here is a user-defined function that should work for any year after 1999:

Function Period(dateValue As Date) As String
Period = Format((Int((dateValue - 23) / 28) - 4) Mod 13 + 1, "00/") & Format(Int((dateValue - 135) / 364) - 100, "00")
End Function

Open in new window


Install this code in a regular module sheet and use it with a worksheet formula like:
=Period(A3)                   assuming A3 contains a date
0
 
JJINFMAuthor Commented:
Thank you.  This is what I was looking for.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now