?
Solved

Where can I get a flat txt file with a perpetual list of future calendar dates

Posted on 2006-04-12
9
Medium Priority
?
302 Views
Last Modified: 2009-07-29
Im trying to create a way to count number of business days between dates. The problem is I need to find a csv or text file with a list of these dates. Does anyone know where I can find this or how can I do this without manually creating it in excel?
I need the file to contain a list from Jan 1 2004 to Dec 31 2018
I need 2 columns date, type,
type = 0(weekends), 1(businessday), 2(holiday)

LH
0
Comment
Question by:LHood1
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 2000 total points
ID: 16439513
Hi Larry,

You could create your own:


Sub LHoodDateFile()
 Dim vFF As Long, vDate As Long, vFile As String
 vFile = "C:\datefile.csv"
 vFF = FreeFile
 Open vFile For Output As #vFF
 For vDate = DateValue("Jan 1, 2004") To DateValue("Dec 31, 2018")
  Print #vFF, Format(vDate, "mm/dd/yyyy") & "," & _
   IIf(Weekday(vDate, vbMonday) > 5, 0, IsHoliday(vDate))
 Next
 Close #vFF
End Sub

Function IsHoliday(ByVal aDate As Long) As Long
 'a list of holidays depends on the company/country/religion/etc,
 ' you can use this function to define it yourself
 IsHoliday = 1
End Function


Matt
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16439683
what programming environment are you using?

AW
0
 

Author Comment

by:LHood1
ID: 16440028
Well Im in a  MS Visual Studio.net environment but I guess I could use the vbscript  provided by Matt to accomplish this.. If you have any other suggestions please advise otherwise im going to try Matts code and let you know how it turns out
LH
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:mvidas
ID: 16440074
Unfortunately I know almost no .net, but I'm sure Arthur or someone else would.  I posted that since I've helped you with vba/vb6 before, you could just paste it into excel vba.  But note that there will be no holiday type codes until the IsHoliday function is modified
0
 

Author Comment

by:LHood1
ID: 16440405
ok thats fine. so in terms of modifying the type codes I simply will need to add the following dates as holidays?
2004 holidays:
Jan 1 & 2    (new years)
Feb. 16      (presidents day)
May 31       (memorial day)
July 5         (Independents day)
Sep 6          (labor day)
Nov 25 & 26  (thanksgiving)
Dec. 24       (xmas)
Dec. 31      (new yrs eve)

2005 holidays:  same as 2011
Jan 3
Feb 21
May 30
July 1
July 4
Sep 5
Nov 24 & 25
Dec 26

2006 holidays: same as 2017
Jan 2
Feb 20
May 29
July 3 & 4
Sep 4
Nov 23 & 24
Dec 22
Dec 25

The above is the only holiday data I could get. Also I listed which future years match calendarwise. I dont have any other holiday dates for future years just 04,05 & 06.

Special Notes:
1. If new years falls on a weekend then we get following monday off.
2. Presidents day is 3rd monday in Feb
3. Memorial day is the last monday in may
4. If independence day falls on monday we get previous friday off if it falls on weekday then we get day before  july 4th off as well. so that would be july 3 & 4th off for holiday that month.
5. Labor day is first monday in Sep
6. Thanksgiving is the fourth thursday in november we get thursday & friday off
7. xmas dec 25  if on monday we get previous friday off. if during normal weekday we always get dec 24th off.

So how would i function this?
0
 
LVL 85

Expert Comment

by:ozo
ID: 16440618
#!/usr/bin/perl
use Date::Manip;
$from=ParseDate("Jan 1 2004");
$to=ParseDate("Dec 31 2018");
for( $date=$from; Date_Cmp($date,$to)<=0; $date=Date_GetNext($date,undef,0,0) ){
    print UnixDate($date,"%b %d %Y, "), UnixDate($date,"%w")>5?0:Date_IsWorkDay($date)?1:2, "\n";
}


0
 

Author Comment

by:LHood1
ID: 16440703
Thanks Matt.. if you can help me out on the function it would greatly be appreciated but for now what you have provided so far works great!

LH
0
 
LVL 85

Expert Comment

by:ozo
ID: 16440792
*Holiday
1/1                             = New Year's Day
Jan 2 = (new years)
third Monday in Feb             = Presidents' Day
1*0:0:0:0:0:0*EASTER            = Easter
1*11:0:11:0:0:0*CWD             = Veteran's Day (observed)
1*0:0:0:0:0:0*EASTER,PD5        = Good Friday
May 31      = (memorial day)
July 5        = (Independents day)
Sep 6         = (labor day)
fourth Thu in Nov               = Thanksgiving
fourth Thu in Nov + 1 day       =
12/25 - 1 business day          =       (xmas)
1*12:0:24:0:0:0*FW1  = Christmas
1*12:0:25:0:0:0*FW1,a = Boxing
Dec. 31      (new yrs eve)
0
 
LVL 35

Expert Comment

by:mvidas
ID: 16449410
Sorry about the delay, just remembered this. I got the notif too late last night to do anything about it.
What happens when july 4th is on a weekend? Do you only get that Friday off? Likewise with Christmas..

I did write your function for you, though to save on processor time I determined the holidays beforehand.  I'm hoping someone else here can write a better one for you.  Here is my (ugly) IsHoliday function, works quick though:

Function IsHoliday(ByVal aDate As Long) As Long
 Select Case aDate
  Case #1/1/2004#, #2/16/2004#, #5/31/2004#, #7/2/2004#, #9/6/2004#, #11/25/2004#, #11/26/2004#, #12/24/2004#: IsHoliday = 2
  Case #1/3/2005#, #2/21/2005#, #5/30/2005#, #7/1/2005#, #7/4/2005#, #9/5/2005#, #11/24/2005#, #11/25/2005#, #12/23/2005#: IsHoliday = 2
  Case #1/2/2006#, #2/20/2006#, #5/29/2006#, #7/3/2006#, #7/4/2006#, #9/4/2006#, #11/23/2006#, #11/24/2006#, #12/22/2006#, #12/25/2006#: IsHoliday = 2
  Case #1/1/2007#, #2/19/2007#, #5/28/2007#, #7/3/2007#, #7/4/2007#, #9/3/2007#, #11/22/2007#, #11/23/2007#, #12/24/2007#, #12/25/2007#: IsHoliday = 2
  Case #1/1/2008#, #2/18/2008#, #5/26/2008#, #7/3/2008#, #7/4/2008#, #9/1/2008#, #11/27/2008#, #11/28/2008#, #12/24/2008#, #12/25/2008#: IsHoliday = 2
  Case #1/1/2009#, #2/16/2009#, #5/25/2009#, #7/3/2009#, #9/7/2009#, #11/26/2009#, #11/27/2009#, #12/24/2009#, #12/25/2009#: IsHoliday = 2
  Case #1/1/2010#, #2/15/2010#, #5/31/2010#, #7/2/2010#, #9/6/2010#, #11/25/2010#, #11/26/2010#, #12/24/2010#: IsHoliday = 2
  Case #1/3/2011#, #2/21/2011#, #5/30/2011#, #7/1/2011#, #7/4/2011#, #9/5/2011#, #11/24/2011#, #11/25/2011#, #12/23/2011#: IsHoliday = 2
  Case #1/2/2012#, #2/20/2012#, #5/28/2012#, #7/3/2012#, #7/4/2012#, #9/3/2012#, #11/22/2012#, #11/23/2012#, #12/24/2012#, #12/25/2012#: IsHoliday = 2
  Case #1/1/2013#, #2/18/2013#, #5/27/2013#, #7/3/2013#, #7/4/2013#, #9/2/2013#, #11/28/2013#, #11/29/2013#, #12/24/2013#, #12/25/2013#: IsHoliday = 2
  Case #1/1/2014#, #2/17/2014#, #5/26/2014#, #7/3/2014#, #7/4/2014#, #9/1/2014#, #11/27/2014#, #11/28/2014#, #12/24/2014#, #12/25/2014#: IsHoliday = 2
  Case #1/1/2015#, #2/16/2015#, #5/25/2015#, #7/3/2015#, #9/7/2015#, #11/26/2015#, #11/27/2015#, #12/24/2015#, #12/25/2015#: IsHoliday = 2
  Case #1/1/2016#, #2/15/2016#, #5/30/2016#, #7/1/2016#, #7/4/2016#, #9/5/2016#, #11/24/2016#, #11/25/2016#, #12/23/2016#: IsHoliday = 2
  Case #1/2/2017#, #2/20/2017#, #5/29/2017#, #7/3/2017#, #7/4/2017#, #9/4/2017#, #11/23/2017#, #11/24/2017#, #12/22/2017#, #12/25/2017#: IsHoliday = 2
  Case #1/1/2018#, #2/19/2018#, #5/28/2018#, #7/3/2018#, #7/4/2018#, #9/3/2018#, #11/22/2018#, #11/23/2018#, #12/24/2018#, #12/25/2018#: IsHoliday = 2
  Case Else: IsHoliday = 1
 End Select
End Function


Heres a function to create the above Select Case statement.  This way if you need to amend a holiday rule or anything it might be easier for you to see how I did it:

Sub LHHolidays()
 Dim Yr As Long, i As Long, vFF As Long
 vFF = FreeFile
 Open "C:\hol.txt" For Output As #vFF
 Print #vFF, " Select Case aDate"
 For Yr = 2004 To 2018
  Print #vFF, "  Case ";

'1. If new years falls on a weekend then we get following monday off.
  i = Weekday(DateValue("jan 1, " & CStr(Yr)), vbMonday)
  If i < 6 Then
   Print #vFF, "#" & Format(DateValue("jan 1, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
  Else
   Print #vFF, "#" & Format(DateValue("jan 1, " & CStr(Yr)) + Abs(i - 8), "mm/dd/yyyy") & "#,";
  End If
 
'2. Presidents day is 3rd monday in Feb
  i = Weekday(DateValue("feb 1, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("feb 1, " & CStr(Yr)) - i + 21, "mm/dd/yyyy") & "#,";

'3. Memorial day is the last monday in may
  i = Weekday(DateValue("may 31, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("may 31, " & CStr(Yr)) - (i Mod 7), "mm/dd/yyyy") & "#,";

'4. If independence day falls on monday we get previous friday off if it falls on weekday
' then we get day before  july 4th off as well. so that would be july 3 & 4th off for
' holiday that month.
  i = Weekday(DateValue("july 4, " & CStr(Yr)), vbMonday)
  Select Case i
   Case 1
    Print #vFF, "#" & Format(DateValue("july 1, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("july 4, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 2 To 5
    Print #vFF, "#" & Format(DateValue("july 3, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("july 4, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 6    'you didnt say what happens when july 4th is weekend, I'm guessing just friday
    Print #vFF, "#" & Format(DateValue("july 3, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
   Case 7
    Print #vFF, "#" & Format(DateValue("july 2, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
  End Select

'5. Labor day is first monday in Sep
  i = Weekday(DateValue("sep 1, " & CStr(Yr)), vbTuesday)
  Print #vFF, "#" & Format(DateValue("sep 1, " & CStr(Yr)) + 7 - i, "mm/dd/yyyy") & "#,";
 
'6. Thanksgiving is the fourth thursday in november we get thursday & friday off
  i = Weekday(DateValue("nov 1, " & CStr(Yr)), vbFriday)
  Print #vFF, "#" & Format(DateValue("nov 1, " & CStr(Yr)) - i + 28, "mm/dd/yyyy") & "#,";
  Print #vFF, "#" & Format(DateValue("nov 1, " & CStr(Yr)) - i + 29, "mm/dd/yyyy") & "#,";

'7. xmas dec 25  if on monday we get previous friday off. if during normal weekday we
' always get dec 24th off.
  i = Weekday(DateValue("dec 25, " & CStr(Yr)), vbMonday)
  Select Case i
   Case 1
    Print #vFF, "#" & Format(DateValue("dec 22, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("dec 25, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 2 To 5
    Print #vFF, "#" & Format(DateValue("dec 24, " & CStr(Yr)), "mm/dd/yyyy") & "#,";
    Print #vFF, "#" & Format(DateValue("dec 25, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 6    'you didnt say what happens when xmas is on a weekend, I'm guessing just friday like jul 4
    Print #vFF, "#" & Format(DateValue("dec 24, " & CStr(Yr)), "mm/dd/yyyy") & "#";
   Case 7
    Print #vFF, "#" & Format(DateValue("dec 23, " & CStr(Yr)), "mm/dd/yyyy") & "#";
  End Select
 
  Print #vFF, ": IsHoliday = 2"
 Next
 Print #vFF, "  Case Else: IsHoliday = 1"
 Print #vFF, " End Select";
 Close #vFF
End Sub

Matt
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question