Tag: excel

My boss, bless ‘im (no really, do; he’s a sound bloke, great guy to work for, and is just getting through some serious health problems), needs a monthly status report on the second last Friday of every month. I live by my calendar applications reminding me to do things, so I thought it’d be no problem getting Outlook to set up a reminder.

No dice; it will only set up appointments on the 1st, 2nd, 3rd etc., starting from the beginning of the month. I did a web search, and really thought I’d found a solution for iCal. It was not to be; this was for a Unix program called ICal; dratted case-insensitive search. Curiously, it appears that the ics spec might support a second-from-last syntax, but Outlook and iCal (and Google Calendar) can’t create them. Phooey.

So I tried excel; and really thought I’d found the basis of an answer: Last Friday of the month. And indeed, most of their assumptions are right; the code

DATE(year,month+1,1)-WEEKDAY(DATE(year,month+1,1),1)

really does give you the date of the last Saturday in the month. But you can’t assume that the day before the last Saturday is the last Friday – it is the second last, if the month ends on a Friday (April 2010 is a test case).

So I tried the Swiss Army chainsaw of brute-force date calculation: Perl with Date::Calc. What I do here is create an array of every Friday in the month, then print the second last member; never known to fail:

#!/usr/bin/perl -w
# second_last_friday.pl - show date of 2nd last friday
use strict;
use Date::Calc qw(Today
  Nth_Weekday_of_Month_Year
  Add_Delta_YMD);
my ( $new_year, $new_month ) = 0;

my ( $year, $month, $day ) = Today;
foreach ( 1 .. 24 ) {
    my @fridays = ();   # for every friday in this month
    foreach my $week ( 1 .. 5 ) {
        if (
            ( $new_year, $new_month, $day ) =
            Nth_Weekday_of_Month_Year(
                $year, $month, 5, $week
            )
          )
        {               # day of week 5 is Friday
            push @fridays, $day;
        }
        else {
            last;       # not a valid Friday
        }
    }
    printf( "%4d/%02d/%02d\n",
        $year, $month, $fridays[-2] );
    ( $year, $month, $day ) =
      Add_Delta_YMD( $year, $month, 1, 0, 1, 0 )
      ;                 # month++
}
exit;

and this gives

2009/11/20
2009/12/18
2010/01/22
2010/02/19
2010/03/19
2010/04/23
2010/05/21

...

See, notice the tricksy 23 April 2010, which – considering thirty days hath April et al – ends on a Friday and threw that simple Excel calculation off.

I’m disappointed that all these new applications like Outlook and iCal don’t seem to handle dates as elegantly as the old unix programs I used to use. pcal, in particular, could generate incredibly complex date formulae. I must dig around to solve this problem – and for now, actually have to remember to write that report on the second last Friday of this month …