Date Calculations

[Neil Edwards]

Hello everyone,

I am working on a set of financial reports but unfortunately the most difficult one is the first one that I need. I need to reformat the "Aged A/R Balances Report" and for that I need to know which invoices are current, which are more than 30 days old, which are 60 days old, and which ones are 90 or more days old. I also need to know the due date which is 10 days from the report date. I tried to use the date command but it doesn't work very well. Is there an easier method?

Thanks in advance, Neil

[A. Clay Stephenson · Accepted solution]

Hi Neil:

Is there an easier method? Does a hog like slop? My universal date tool is called caljd.sh; it's my hammer and every date problem is a nail. The key to doing your kind of date problems is to convert the nasty months, days, and years to a Julian Day. That's the number of days since 4713 BCE and is used by astronomers to make orbital calculations much easier. You can call the same scripot to reverse the calculations to get back months, days, and years. If your convert MDY to a Julian Day the $JD - 30 is
your 30 day date, $JD - 60 is your 60 day date and so on. Caljd.sh will even do those calculations for you.

I would do something like this:
#!/usr/bin/sh
#
MO=2
DA=14
YR=2002

JD=$(caljd.sh ${MO} ${DA} ${YR})
JD30=$((${JD} - 30))
JD60=$((${JD} - 60))
JD90=$((${JD} - 90))

INVOICE_MO=1
INVOICE_DA=3
INVOICE_YR=2002
JD_INVOICE=$(caljd.sh ${INVOICE_MO} ${INVOICE_DA} ${INVOICE_YR})

echo "Report Date = ${JD}"
echo "Invoice Date = ${JD_INVOICE}"

DUE_DATE=$(caljd.sh $(caljd.sh -n 10))
echo "Due date = ${DUE_DATE}"
DUE_DATE=$(caljd.sh $(caljd.sh -n 10 -x 0 -x 6))
echo "Due date skipping weekends = ${DUE_DATE}"
DUE_DATE=$(caljd.sh $(caljd.sh -n 10 -x 0 -x 6 -h))
echo "Due date skipping weekends and holidays = ${DUE_DATE}"

The nice part about this method is that year boundaries don't matter; month boundaries don't matter; and even leap years don't matter.
You can do a caljd.sh -u for full usage.

Enjoy, Clay

[A. Clay Stephenson]

Hi again Neil:

I just remembered that you were the guy askings about awk vs. Perl. If you chose Perl, you can still call the above caljd.sh from within your Perl but you can execute much faster if you add the equivalent subroutines to your Perl. There is also a module, Date::Calc, that contains a Delta_Days routine that you will find useful. Anyway, here are the equivalent subroutines that do the Julian Day caluculations in Perl. Nore the use of the int subroutine. Perl (like awk) does floating point math so we need to force integer arithmetic so that 5/2 = 2 rather than 2.5.
I attached a little bit of example usage at the end.

#!/usr/bin/perl
use strict;


sub int
{
my $x = $_[0];
return(sprintf("%d",$x));
} # int

sub cal_jdate
{
my $mo = $_[0];
my $da = $_[1];
my $yr = $_[2];
my $jd = $da - 32075 + int(1461 * ($yr + 4800 + int(($mo - 14)/12)) / 4) +
int(int(367 * ($mo - 2 - int(($mo - 14)/12) * 12))/12) -
int(int(3 * (($yr + 4900 + int(($mo - 14)/12))/100))/4);
return($jd);
} # cal_jdate


sub jdate_cal
{
my $jd = $_[0];
my ($l,$n,$ll,$i,$j,$da,$mo,$yr);

$l = $jd + 68569;
$n = int((4 * $l) / 146097);
$ll = int((146097 * $n + 3) / 4);
$l -= $ll;
$i = int((4000 * ($l + 1)) / 1461001);
$ll = int((1461 * $i) / 4);
$l = $l - $ll + 31;
$j = int((80 * $l) / 2447);
$da = $l - int((2447 * $j) / 80);
$l = int($j / 11);
$mo = $j + 2 - (12 * $l);
$yr = 100 * ($n - 49) + $i + $l;
return($mo,$da,$yr);
} # jdate_cal


sub wkday
{
my $x = $_[0];
return(int(($x + 1) % 7));
} # wkday


# get current month, day, and year
sub today
{
my ($mo,$da,$yr) = (localtime)[4,3,5];
$yr += 1900; # years returned by localtime() are years since 1900
$mo++; # months returned by localtime range from 0 to 11
return($mo,$da,$yr)
} # today

# ----------------------------------------------------------------------
# --------------------- Examples ---------------------------------------
# ----------------------------------------------------------------------

printf("\n");
# convert a Julian Day to month, day, year
my $JD = 2452313;
my ($MO,$DA,$YR) = jdate_cal($JD);
printf("Julian Day %8d: Month = %02d Day = %02d Year = %04d\n",
$JD,$MO,$DA,$YR);

printf("\n");
# get current month, day, and year
($MO,$DA,$YR) = today();
printf("Current Date: Month = %02d Day = %02d Year = %04d\n",$MO,$DA,$YR);
printf("\n");
# convert current date to Julian Day
$JD = cal_jdate($MO,$DA,$YR);
printf("Julian Day = %8d ",$JD);
# Determine day of week - 0 - Sun; 6 - Sat.
my $WDAY = wkday($JD);
printf("Day of Week: %d\n",$WDAY);
printf("\n");


# -----------------------------------------------------------
# Skip 10 days from given date unless that falls on a weekend

$MO = 2;
$DA = 12;
$YR = 2002;
my $skip = 10; # number of days ahead

$JD = cal_jdate($MO,$DA,$YR) + $skip;
$WDAY = wkday($JD);
while (($WDAY == 0) || ($WDAY == 6))
{
$JD++;
$WDAY = wkday($JD);
}
printf("%d days from %02d/%02d/%04d is Julian Day %d ",$skip,$MO,$DA,$YR,$JD);
my ($MO10,$DA10,$YR10) = jdate_cal($JD);
printf("(%02d/%02d/%04d)\n",$MO10,$DA10,$YR10);

Regards, Clay

[A. Clay Stephenson]

Hi (once again) Neil:

I'm an idiot, I should have mentioned that the Date::Calc module is not part of a standard perl distribution. You must download it from http://www.perl.org/CPAN and install it.

Clay

[Ian Dennison_1]

I have a somewhat simplistic method of calculating dates, in that I have 2 files (Leap and Non-Leap) that contain 365 or 366 lines, listing Julian and DD-MM dates (space separated) for the entire year.

Julian Day# Mon# MonName
001 01 01 Jan
002 02 01 Jan
003 03 01 Jan

All I do is find the Julian Date I need, subtract (with year wraparound) the amount I need, then search again to find the DD-MM Date.

Long-winded to set up, dead simple to operate with.

Cheers, Ian Dennison