PLEASE HELP with CALC for MIN & MAX

#1

I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them to get
a grand total # of days (TNOD). The street cost is divided by TNOD for a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#2

It appears that 4/1/2006 isn't being taken into account. (I get 91 days
when adding them up)

--
---------------------------------------------------------
Tony McGuire

#3

Jeanette,

1 through 10, inclusive, includes a total of 10 numbers.

10 - 1 = 9

1 is subtracted, so there's only 9 numbers left.

The first date range (2005) has 108 days, inclusive. The second has 91 days
(inclusive). Together, there are 199 days (inclusive).

However, when you're doing date math, "EndDate - StartDate" **subtracts the
start date**. In other words, the StartDate is not being included in the
count of days. If you want to be inclusive, do:

(EndDate-StartDate)+1

....then you'll get 108 days in 2005, 91 days in 2006 and 199 days total.

HTH,

Liz

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:

Quote:

I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them to get
a grand total # of days (TNOD). The street cost is divided by TNOD for a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#4

Helpful but...
the idea is to get a daily cost and project it given the BGN & END dates
and then the amounts should roll up to the original StreetCost.

It's not happening if I use 199 days for the example below.
HOWEVER if the BGN & END dates were not broken up by a RptYear the TNOD would
be 198 and that allows an accurate rollup to the StreetCost BUT as (END-BGN).

AND using dates 1/15/06 thru 2/1/06 and 9/15/05 thru 12/31/05 for 2 seperate
streets (END-BGN)+1 does not allow an accurate rollup to the StreetCost -
it does if (END-BGN)is used...

WHAT TO DO ????

"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote:

Quote:

Jeanette,

1 through 10, inclusive, includes a total of 10 numbers.

10 - 1 = 9

1 is subtracted, so there's only 9 numbers left.

The first date range (2005) has 108 days, inclusive. The second has 91
days
(inclusive). Together, there are 199 days (inclusive).

However, when you're doing date math, "EndDate - StartDate" **subtracts
the
start date**. In other words, the StartDate is not being included in the
count of days. If you want to be inclusive, do:

(EndDate-StartDate)+1

...then you'll get 108 days in 2005, 91 days in 2006 and 199 days total.

HTH,

Liz

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:

I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them to
get
a grand total # of days (TNOD). The street cost is divided by TNOD for
a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#5

I don't know how else to explain it. 198 excludes the start date from
the calculation. 90 and 107 exclude two start dates from the
calculation. If you want to exclude the start date, I reckon you'll
have to:

a) use the min start and max end - gets you 198

OR

b) count how many rows there are for the given street, sum the two
ranges and add n-1, where n is the number of rows.

Or something.

Liz

Jeanette wrote:

Quote:

Helpful but...
the idea is to get a daily cost and project it given the BGN & END dates
and then the amounts should roll up to the original StreetCost.

It's not happening if I use 199 days for the example below.
HOWEVER if the BGN & END dates were not broken up by a RptYear the TNOD would
be 198 and that allows an accurate rollup to the StreetCost BUT as (END-BGN).

AND using dates 1/15/06 thru 2/1/06 and 9/15/05 thru 12/31/05 for 2 seperate
streets (END-BGN)+1 does not allow an accurate rollup to the StreetCost -
it does if (END-BGN)is used...

WHAT TO DO ????

"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote:
Jeanette,

1 through 10, inclusive, includes a total of 10 numbers.

10 - 1 = 9

1 is subtracted, so there's only 9 numbers left.

The first date range (2005) has 108 days, inclusive. The second has 91
days
(inclusive). Together, there are 199 days (inclusive).

However, when you're doing date math, "EndDate - StartDate" **subtracts
the
start date**. In other words, the StartDate is not being included in the
count of days. If you want to be inclusive, do:

(EndDate-StartDate)+1

...then you'll get 108 days in 2005, 91 days in 2006 and 199 days total.

HTH,

Liz

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:
I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them to
get
a grand total # of days (TNOD). The street cost is divided by TNOD for
a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#6

You're OK, I undersood what you said, I BELIEVE.

Even though I know the ASSUME THEORY I ASSUMED the # would be the actual
# of days like 12/31/05-1/1/05 would be 365 and not 364.

I guess I am being too wordy. Are there any SPECIFIC rules regarding the
arithmentic of Dates? I'm looking for some rules about when a date range
will be INCLUSIVE.

It seems like:
a) 02/1/06-1/15/06 is INCLUSIVE (17)
b) 12/31/05-9/15/05 is INCLUSIVE (107)
BUT
c) 10/1/06-1/1/06 is NOT INCLUSIVE (9)

I don't understand why a&b are INCLUSIVE and c is NOT...

SORRY - maybe it's in my face and I just don't see it !

Liz McGuire <liz (AT) paradoxcommunity (DOT) com> wrote:

Quote:

I don't know how else to explain it. 198 excludes the start date from
the calculation. 90 and 107 exclude two start dates from the
calculation. If you want to exclude the start date, I reckon you'll
have to:

a) use the min start and max end - gets you 198

OR

b) count how many rows there are for the given street, sum the two
ranges and add n-1, where n is the number of rows.

Or something.

Liz

Jeanette wrote:
Helpful but...
the idea is to get a daily cost and project it given the BGN & END dates
and then the amounts should roll up to the original StreetCost.

It's not happening if I use 199 days for the example below.
HOWEVER if the BGN & END dates were not broken up by a RptYear the TNOD
would
be 198 and that allows an accurate rollup to the StreetCost BUT as (END-BGN).

AND using dates 1/15/06 thru 2/1/06 and 9/15/05 thru 12/31/05 for 2 seperate
streets (END-BGN)+1 does not allow an accurate rollup to the StreetCost
-
it does if (END-BGN)is used...

WHAT TO DO ????

"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote:
Jeanette,

1 through 10, inclusive, includes a total of 10 numbers.

10 - 1 = 9

1 is subtracted, so there's only 9 numbers left.

The first date range (2005) has 108 days, inclusive. The second has
91
days
(inclusive). Together, there are 199 days (inclusive).

However, when you're doing date math, "EndDate - StartDate" **subtracts
the
start date**. In other words, the StartDate is not being included in
the
count of days. If you want to be inclusive, do:

(EndDate-StartDate)+1

...then you'll get 108 days in 2005, 91 days in 2006 and 199 days total.

HTH,

Liz

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:
I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them
to
get
a grand total # of days (TNOD). The street cost is divided by TNOD for
a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would
have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#7

Don't know if this helps but the first two cross month boundaries and the
third one is in the same month.

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote

Quote:

You're OK, I undersood what you said, I BELIEVE.

Even though I know the ASSUME THEORY I ASSUMED the # would be the actual
# of days like 12/31/05-1/1/05 would be 365 and not 364.

I guess I am being too wordy. Are there any SPECIFIC rules regarding the
arithmentic of Dates? I'm looking for some rules about when a date range
will be INCLUSIVE.

It seems like:
a) 02/1/06-1/15/06 is INCLUSIVE (17)
b) 12/31/05-9/15/05 is INCLUSIVE (107)
BUT
c) 10/1/06-1/1/06 is NOT INCLUSIVE (9)

I don't understand why a&b are INCLUSIVE and c is NOT...

SORRY - maybe it's in my face and I just don't see it !

Liz McGuire <liz (AT) paradoxcommunity (DOT) com> wrote:
I don't know how else to explain it. 198 excludes the start date from
the calculation. 90 and 107 exclude two start dates from the
calculation. If you want to exclude the start date, I reckon you'll
have to:

a) use the min start and max end - gets you 198

OR

b) count how many rows there are for the given street, sum the two
ranges and add n-1, where n is the number of rows.

Or something.

Liz

Jeanette wrote:
Helpful but...
the idea is to get a daily cost and project it given the BGN & END dates
and then the amounts should roll up to the original StreetCost.

It's not happening if I use 199 days for the example below.
HOWEVER if the BGN & END dates were not broken up by a RptYear the TNOD
would
be 198 and that allows an accurate rollup to the StreetCost BUT as
(END-BGN).

AND using dates 1/15/06 thru 2/1/06 and 9/15/05 thru 12/31/05 for 2
seperate
streets (END-BGN)+1 does not allow an accurate rollup to the StreetCost
-
it does if (END-BGN)is used...

WHAT TO DO ????

"Liz McGuire" <liz (AT) paradoxcommunity (DOT) com> wrote:
Jeanette,

1 through 10, inclusive, includes a total of 10 numbers.

10 - 1 = 9

1 is subtracted, so there's only 9 numbers left.

The first date range (2005) has 108 days, inclusive. The second has
91
days
(inclusive). Together, there are 199 days (inclusive).

However, when you're doing date math, "EndDate - StartDate" **subtracts
the
start date**. In other words, the StartDate is not being included in
the
count of days. If you want to be inclusive, do:

(EndDate-StartDate)+1

...then you'll get 108 days in 2005, 91 days in 2006 and 199 days
total.

HTH,

Liz

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote:
I am creating a report that shows construction Cost projections.
Given the bgn & end dates Costs are shown via Month and Year.
For each street I calc the # of days between bgn & end and sum them
to
get
a grand total # of days (TNOD). The street cost is divided by TNOD for
a
daily cost.

here's the hitch - in the example below:
2005 # = 107
2006 # = 90 for a TNOD of 197
if I were able to get the # of days via MAX(end) - MIN(bgn) I would
have
a TNOD of 198 since the dates are consectuve months.

Can you help me code this occurence?

Year StreetCODE StreetCost Begin End
2005 06520 $282,208.40 09/15/2005 12/31/2005
2006 06520 $282,208.40 01/01/2006 04/01/2006

#8

Jeanette wrote:

Quote:

You're OK, I undersood what you said, I BELIEVE.

Even though I know the ASSUME THEORY I ASSUMED the # would be the actual
# of days like 12/31/05-1/1/05 would be 365 and not 364.

I guess I am being too wordy. Are there any SPECIFIC rules regarding the
arithmentic of Dates? I'm looking for some rules about when a date range
will be INCLUSIVE.

It seems like:
a) 02/1/06-1/15/06 is INCLUSIVE (17)
b) 12/31/05-9/15/05 is INCLUSIVE (107)
BUT
c) 10/1/06-1/1/06 is NOT INCLUSIVE (9)

I don't understand why a&b are INCLUSIVE and c is NOT...

SORRY - maybe it's in my face and I just don't see it !

Jeanette,

If you include both the 15th and the 1st, a) is 18; b) if you include
both the 15th and the 31st is 108; and c) seems to use a different date
format (october 1 to january 1, 9 days, huh?) - or, it's january 10:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10 - ten days, inclusive.

What you're doing is subtracting the low date from the high date and in
all cases, the low date is excluded, just like when you subtract 1 from
10 the 1 is excluded. All of those behave the same way - all are NOT
inclusive of the first day.

Get a calendar - a printed one - manually count the first day in the
range as 1 and each day up through the last day. If you include both
the first and last days, you'll find that all of those numbers below
exclude the start date.

Or:
a)
1 15 Jan 2006
2 16 Jan 2006
3 17 Jan 2006
4 18 Jan 2006
5 19 Jan 2006
6 20 Jan 2006
7 21 Jan 2006
8 22 Jan 2006
9 23 Jan 2006
10 24 Jan 2006
11 25 Jan 2006
12 26 Jan 2006
13 27 Jan 2006
14 28 Jan 2006
15 29 Jan 2006
16 30 Jan 2006
17 31 Jan 2006
18 01 Feb 2006

b)
1 15 Sep 2005
2 16 Sep 2005
3 17 Sep 2005
4 18 Sep 2005
5 19 Sep 2005
6 20 Sep 2005
7 21 Sep 2005
8 22 Sep 2005
9 23 Sep 2005
10 24 Sep 2005
11 25 Sep 2005
12 26 Sep 2005
13 27 Sep 2005
14 28 Sep 2005
15 29 Sep 2005
16 30 Sep 2005
17 01 Oct 2005
18 02 Oct 2005
19 03 Oct 2005
20 04 Oct 2005
21 05 Oct 2005
22 06 Oct 2005
23 07 Oct 2005
24 08 Oct 2005
25 09 Oct 2005
26 10 Oct 2005
27 11 Oct 2005
28 12 Oct 2005
29 13 Oct 2005
30 14 Oct 2005
31 15 Oct 2005
32 16 Oct 2005
33 17 Oct 2005
34 18 Oct 2005
35 19 Oct 2005
36 20 Oct 2005
37 21 Oct 2005
38 22 Oct 2005
39 23 Oct 2005
40 24 Oct 2005
41 25 Oct 2005
42 26 Oct 2005
43 27 Oct 2005
44 28 Oct 2005
45 29 Oct 2005
46 30 Oct 2005
47 31 Oct 2005
48 01 Nov 2005
49 02 Nov 2005
50 03 Nov 2005
51 04 Nov 2005
52 05 Nov 2005
53 06 Nov 2005
54 07 Nov 2005
55 08 Nov 2005
56 09 Nov 2005
57 10 Nov 2005
58 11 Nov 2005
59 12 Nov 2005
60 13 Nov 2005
61 14 Nov 2005
62 15 Nov 2005
63 16 Nov 2005
64 17 Nov 2005
65 18 Nov 2005
66 19 Nov 2005
67 20 Nov 2005
68 21 Nov 2005
69 22 Nov 2005
70 23 Nov 2005
71 24 Nov 2005
72 25 Nov 2005
73 26 Nov 2005
74 27 Nov 2005
75 28 Nov 2005
76 29 Nov 2005
77 30 Nov 2005
78 01 Dec 2005
79 02 Dec 2005
80 03 Dec 2005
81 04 Dec 2005
82 05 Dec 2005
83 06 Dec 2005
84 07 Dec 2005
85 08 Dec 2005
86 09 Dec 2005
87 10 Dec 2005
88 11 Dec 2005
89 12 Dec 2005
90 13 Dec 2005
91 14 Dec 2005
92 15 Dec 2005
93 16 Dec 2005
94 17 Dec 2005
95 18 Dec 2005
96 19 Dec 2005
97 20 Dec 2005
98 21 Dec 2005
99 22 Dec 2005
100 23 Dec 2005
101 24 Dec 2005
102 25 Dec 2005
103 26 Dec 2005
104 27 Dec 2005
105 28 Dec 2005
106 29 Dec 2005
107 30 Dec 2005
108 31 Dec 2005

c)
1 01 Jan 2006
2 02 Jan 2006
3 03 Jan 2006
4 04 Jan 2006
5 05 Jan 2006
6 06 Jan 2006
7 07 Jan 2006
8 08 Jan 2006
9 09 Jan 2006
10 10 Jan 2006

Liz

#9

That's true, Rick, but the date math is correct in all cases. High date
minus low date is the numbers she shows and those numbers are correct
and consistent - all are NOT inclusive of the first day, which is basic
math.

Liz

Rick Rans wrote:

Quote:

Don't know if this helps but the first two cross month boundaries and the
third one is in the same month.

"Jeanette" <harrisjr (AT) bwsc (DOT) org> wrote in message
news:45d0c915$1 (AT) pnews (DOT) thedbcommunity.com...
You're OK, I undersood what you said, I BELIEVE.

Even though I know the ASSUME THEORY I ASSUMED the # would be the actual
# of days like 12/31/05-1/1/05 would be 365 and not 364.

I guess I am being too wordy. Are there any SPECIFIC rules regarding the
arithmentic of Dates? I'm looking for some rules about when a date range
will be INCLUSIVE.

It seems like:
a) 02/1/06-1/15/06 is INCLUSIVE (17)
b) 12/31/05-9/15/05 is INCLUSIVE (107)
BUT
c) 10/1/06-1/1/06 is NOT INCLUSIVE (9)

I don't understand why a&b are INCLUSIVE and c is NOT...

SORRY - maybe it's in my face and I just don't see it !

#10

On 12 Feb 2007 15:07:49 -0500, Jeanette wrote:

Quote:

I guess I am being too wordy. Are there any SPECIFIC rules regarding the
arithmentic of Dates? I'm looking for some rules about when a date range
will be INCLUSIVE.

It seems like:
a) 02/1/06-1/15/06 is INCLUSIVE (17)
b) 12/31/05-9/15/05 is INCLUSIVE (107)
BUT
c) 10/1/06-1/1/06 is NOT INCLUSIVE (9)

IIUC, Paradox does *not* do anything special with dates. It just converts
them to integers, and performs normal integer arithmetic. It counts (I
think) 30 Dec 1899 as Day 0, then every succeeding day gets the next
integer. So, if you subtract:
3 Jan 1900 - 31 Dec 1899
Paradox converts this to
4 - 1 (the 4th day after 30 Dec 1899, minus the 1st day after)
and returns 3.

HTH

Jim Hargan