JJINFM
asked on
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
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
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
regards, barry
=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
regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. This is what I was looking for.
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