From: flavio@cflav.sublink.org (Flavio Casetta)
Newsgroups: comp.sys.hp48
Subject: Re: Calculating working days between dates
Keywords: Spreadsheet
Date: 6 Oct 92 09:34:41 GMT
Organization: Flavio Casetta, Reg'd HP ISV & OEM of handheld computers.

Dear friends,

the problem of calculating working days between two dates results of
a certain interest especially thinking at the capabilities of the 
Spreadsheet recently presented.

Knowing the exact number of the working days lets you easily reckon
a salary, or estabilish a deadline based on the actual number of the
days (if you need 30 days to do a job, you should specify whether you need
30 working days or a net month).

Making such calculations with the Spreadsheet is a matter of seconds.

First of all let's consider the problem of finding working days.
We need a reference day (in the past) on which basing our calculation.

I choose Monday, January 2 1989.

Now, thanks to the command DDAYS we can calculate the total number of
days between 1/2/1989 and the first date. Suppose that the second date
is October 3 1992.
Pay attention to the current date format flag (-42)

1371 = days1

Repeat the calculation with the second date (10/11/92):

1378 = days2

Now you must extract working days from each number.
Since we computed the difference starting on Monday, the total number of
working days is represented by:

W(days)=IP(days/7)*5+IFTE(days MOD 7==6,5,days MOD 7)

DeltaWdays(days1,days2)=(ABS(W(days1)-W(days2)))  Where Delta is the character
                                                  [alpha] [blue] [C]

DeltaWdays(ABS(W(1371)-W(1378))) = 5

Although the starting day and the arrival day occur on Sunday, the working
days are computed correctly.

These are the raw formulas. Note that the definition of W is
based on a difference of days rather than a date.
The implementation I will explain a bit later, solves this problem.

Now we will put all together with the aid of the Spreasheet.
We will suppose to have flag -42 clear (US format).
First define the function DeltaD(date1,date2) returning DDAYS.

<< -> d1 d2 << d1 d2 DDAYS ABS >> >>
'DeltaD' STO

then define the function W(date)

<< -> date
   << -42 FS? 2.011989 1.021989 IFTE
       date DDAYS -> d
       'IP(d/7)*5+IFTE(d MOD 7==6,5,d MOD 7)'
   >>
>>

'W' STO

and finally the function 
'DeltaW(d1,d2)=(ABS(W(d1)-W(d2)))'

          1            2                 3                       4

1  "Starting Day" "Arrival Day"     "Days total"           "Working days"
2   10.031992      10.111992   'DeltaD(Row(1),Row(2))' 'DeltaW(Row(1),Row(2))'
3   10.311992      11.301992   'DeltaD(Row(1),Row(2))' 'DeltaW(Row(1),Row(2))'

The Spreadsheet will show:

          1            2              3               4

1   Starting Day   Arrival Day     Days total     Working days 
2   10.061992      10.311992           8               5
3   10.311992      11.301992          30              20


the possibility of chaining calculation is one of the most appealing
features of the Spreadsheet.
Suppose you want to know the amount of working days month by month.

Define these functions

Mon$:

<< -> d
   << { "January" "February" "March" "April" ... "December" }
     IF -42 FS? THEN
       d FP 100 * IP
     ELSE 
       d IP
     END
     GET NEWOB
    >>
>>

FD:                    First day of month

<< -> d
   << IF -42 FS? THEN
      d FP 1 +
      ELSE 
      d DUP IP SWAP FP 100 * 1 + 100 / +
      END
   >>
>>

NxMhD:                   First day of next month

<< -> d
   << d FD 31 DATE+ FD
   >>
>>



       1               2               3

1     ""             "Month"       "Working days"
2  1.011993         'Mon$(Row(1))' 'DeltaW(Row(1),NxMhD(Row(1)))'
3 'NxMhD(Col(~-1))' 'Mon$(Row(1))' 'DeltaW(Row(1),NxMhD(Row(1)))'
4 'NxMhD(Col(~-1))'    ...            ...
5     ...              ...            ...

The Spreadsheet will show:


       1             2             3

1                    Month    Working days
2  1.011993        January              21
3  2.011993       February              20
4  3.011993          March              23
5  4.011993          April              22

Now if you change the date in the cell (2,1) from 1.011993 to 10.011993
the spreadsheet automatically shows:

       1             2             3

1                     Month   Working days
2  10.011993        October             21
3  11.011993       November             22
4  12.011993       December             23
5   1.011994        January             21


That's all

__
* flavio@cflav.sublink.org (Flavio Casetta)
 ---------------------------------------------------------------------
| The usual disclaimer does not apply      |   +59 440404 (Vox populi) |
| ForCALC's opinions are necessarily mine  |   +59 304490 (Fax dei)    |
Newsgroups: comp.sys.hp48