| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function: Two Month Date Spans From Fiscal Period - an example
No question here, just a function for the archive.
Search terms: return two month increment time spread range 2 month periods from current fiscal period going backward 2-month increment two-month spread 12 months of 2 two month increment time day slot slots number date 6 Function TwoMonthIncBackwards(FPNumber As Long, Increment As Long) As String 'Returns a two-month spread based on the Fiscal Period Number 'FP 1 = July. Increment is the 2 month range value going backwards 'from the prompted Fiscal Period #. Select Case FPNumber Case 1 Select Case Increment Case 1 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 2 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 3 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 4 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 5 TwoMonthIncBackwards = "Apr 1 - May 31" Case 6 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 2 Select Case Increment Case 1 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 2 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 3 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 4 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 5 TwoMonthIncBackwards = "May 1 - Jun 30" Case 6 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 3 Select Case Increment Case 1 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 2 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 3 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 4 TwoMonthIncBackwards = "Apr 1 - May 31" Case 5 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 6 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 4 Select Case Increment Case 1 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 2 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 3 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 4 TwoMonthIncBackwards = "May 1 - Jun 30" Case 5 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 6 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 5 Select Case Increment Case 1 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 2 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 3 TwoMonthIncBackwards = "Apr 1 - May 31" Case 4 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 5 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 6 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 6 Select Case Increment Case 1 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 2 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 3 TwoMonthIncBackwards = "May 1 - Jun 30" Case 4 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 5 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 6 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 7 Select Case Increment Case 1 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 2 TwoMonthIncBackwards = "Apr 1 - May 31" Case 3 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 4 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 5 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 6 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 8 Select Case Increment Case 1 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 2 TwoMonthIncBackwards = "May 1 - Jun 30" Case 3 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 4 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 5 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 6 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case Else TwoMonthIncBackwards = "Error" End Select Case 9 Select Case Increment Case 1 TwoMonthIncBackwards = "Apr 1 - May 31" Case 2 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 3 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 4 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 5 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 6 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 10 Select Case Increment Case 1 TwoMonthIncBackwards = "May 1 - Jun 30" Case 2 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 3 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 4 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 5 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 6 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 11 Select Case Increment Case 1 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 2 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 3 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 4 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 5 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 6 TwoMonthIncBackwards = "Apr 1 - May 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 12 Select Case Increment Case 1 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 2 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 3 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 4 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 5 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 6 TwoMonthIncBackwards = "May 1 - Jun 30" Case Else TwoMonthIncBackwards = "Error" End Select Case Else End Select End Function |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Function: Two Month Date Spans From Fiscal Period - an example
See Chip Pearson's page on working with dates.
http://www.cpearson.com/excel/datetime.htm#AddingDates -- Regards, Tom Ogilvy "DataFreakFromUtah" wrote in message om... No question here, just a function for the archive. Search terms: return two month increment time spread range 2 month periods from current fiscal period going backward 2-month increment two-month spread 12 months of 2 two month increment time day slot slots number date 6 Function TwoMonthIncBackwards(FPNumber As Long, Increment As Long) As String 'Returns a two-month spread based on the Fiscal Period Number 'FP 1 = July. Increment is the 2 month range value going backwards 'from the prompted Fiscal Period #. Select Case FPNumber Case 1 Select Case Increment Case 1 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 2 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 3 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 4 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 5 TwoMonthIncBackwards = "Apr 1 - May 31" Case 6 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 2 Select Case Increment Case 1 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 2 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 3 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 4 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 5 TwoMonthIncBackwards = "May 1 - Jun 30" Case 6 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 3 Select Case Increment Case 1 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 2 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 3 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 4 TwoMonthIncBackwards = "Apr 1 - May 31" Case 5 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 6 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 4 Select Case Increment Case 1 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 2 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 3 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 4 TwoMonthIncBackwards = "May 1 - Jun 30" Case 5 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 6 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 5 Select Case Increment Case 1 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 2 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 3 TwoMonthIncBackwards = "Apr 1 - May 31" Case 4 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 5 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 6 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 6 Select Case Increment Case 1 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 2 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 3 TwoMonthIncBackwards = "May 1 - Jun 30" Case 4 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 5 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 6 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 7 Select Case Increment Case 1 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 2 TwoMonthIncBackwards = "Apr 1 - May 31" Case 3 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 4 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 5 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 6 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 8 Select Case Increment Case 1 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 2 TwoMonthIncBackwards = "May 1 - Jun 30" Case 3 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 4 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 5 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 6 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case Else TwoMonthIncBackwards = "Error" End Select Case 9 Select Case Increment Case 1 TwoMonthIncBackwards = "Apr 1 - May 31" Case 2 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 3 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 4 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 5 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 6 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 10 Select Case Increment Case 1 TwoMonthIncBackwards = "May 1 - Jun 30" Case 2 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 3 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 4 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 5 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 6 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case Else TwoMonthIncBackwards = "Error" End Select Case 11 Select Case Increment Case 1 TwoMonthIncBackwards = "Jun 1 - Jul 31" Case 2 TwoMonthIncBackwards = "Aug 1 - Sep 30" Case 3 TwoMonthIncBackwards = "Oct 1 - Nov 30" Case 4 TwoMonthIncBackwards = "Dec 1 - Jan 31" Case 5 TwoMonthIncBackwards = "Feb 1 - Mar 31" Case 6 TwoMonthIncBackwards = "Apr 1 - May 31" Case Else TwoMonthIncBackwards = "Error" End Select Case 12 Select Case Increment Case 1 TwoMonthIncBackwards = "Jul 1 - Aug 31" Case 2 TwoMonthIncBackwards = "Sep 1 - Oct 31" Case 3 TwoMonthIncBackwards = "Nov 1 - Dec 31" Case 4 TwoMonthIncBackwards = "Jan 1 - Feb 28" Case 5 TwoMonthIncBackwards = "Mar 1 - Apr 30" Case 6 TwoMonthIncBackwards = "May 1 - Jun 30" Case Else TwoMonthIncBackwards = "Error" End Select Case Else End Select End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Fiscal Month Formula | Excel Discussion (Misc queries) | |||
| =month() function equivalent for fiscal calendar | Excel Worksheet Functions | |||
| Defining fiscal month for pivot table | Excel Discussion (Misc queries) | |||
| Lookup dates, fiscal period table | Excel Worksheet Functions | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Linear Mode
