|
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 |
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 |
| All times are GMT +1. The time now is 03:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com