Rb date formula

Yahoo Archive
1
Shirley,

I tried the altered formula and it still seems the same except now the report is grouping on multiple days instead of weeks. What I found wrong is so simple if I would have studied a calander a little bit. The "01/01/01" date reference is the problem. It throws some due dates into the next week because it "01/01/01" isn't the first day of the week. By changing the reference date to "12/31/00" all the due dates show in the proper week. The only problem now is I need to change the formula to subtract 52 from the week numbers after 52 weeks or after the first week of the year, change the date reference to "12/30/01", and all weeks after week 1 will be correct.

Thanks for all the help
dennis
----- Original Message -----
From: Shirley Graver
To: vantage@yahoogroups.com
Sent: Wednesday, December 19, 2001 9:56 AM
Subject: RE: [Vantage] Rb date formula


Dennis,
Ok here goes,
In the formula section "IIF((JobHead.ReqDueDate -
DATE("01/01/01")) Modulo 7 =0
The formula is asking if the remainder of 9/11/01 minus 01/01/01
has a remainder of ZERO then use the first formula, if not use the second
formula.

The first formula " 09/01/01 minus 01/01/01 equals 253 days, the 253 days
is then divided by 7 which equals 36 or week 36."

The second formula ), (TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) /
7,0) + 1)) is really the same formula as the first except that this part
"TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0)" is only used if
the remainder is NOT zero. The calculation is then TRUNCATED to zero
decimal places. IE: if (JobHead.ReqDueDate - DATE("01/01/01")) actually
equaled 242.56 days, then the formula would TRIM off the .56 days then add a
WHOLE DAY to the formula. And as I typed that last sentence I saw the error
in the formula. The formula should actually be

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7
=0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE(JobHead.ReqDueDate - DATE("01/01/01"),0) + 1) / 7)

This formula adds a DAY when the calculation is not zero, the other
calculation adds a WEEK when the calculation is not zero.

Maybe this is why your weeks are off dennis.
Shirley H. Graver
(End User)
Systems Administrator
Rubber Associates Inc.

-----Original Message-----
From: Dennis Houghton [mailto:dennis@...]
Sent: Wednesday, December 19, 2001 9:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Rb date formula

I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the
week of the year. Their are some dates show up that should be in the
preceding week. I can't see any logical reason for this. I don't exactly
understand how you add/subtract dates and convert to weeks of the year.
Would someone please break this formula down and explain it for me. I don't
understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]




Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=178320.1681224.3270152.1261774/D=egroupweb/S=17050071
83:HM/A=879173/R=0/*http://www.fastweb.com/ib/yahoo-76f>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor



Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
2
I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the week of the year. Their are some dates show up that should be in the preceding week. I can't see any logical reason for this. I don't exactly understand how you add/subtract dates and convert to weeks of the year. Would someone please break this formula down and explain it for me. I don't understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]
3
MODULO is defined in report builder as follows:
expression An integer expression (a constant, field name, or
expression whose value is an integer). The expression must be greater than 0
for MODULO to return a correct value.
base A positive integer expression (a constant, field name or expression
whose value is integer) that is the modulo base. For example, angles
measured in degrees use a base of 360 for modulo arithmetic. The result of
the expression "372 MODULO 360" is 12.

Truncate is defined in report builder as follows:
expression A numeric value (a constant, field name, or expression
that results in a numeric value) that you want to truncate.
decimal-places A non-negative integer expression that indicates the number
of decimal places you want to result from truncating the expression.

The formula is truncating to 0 decimal places.

I'll go through the formula, if someone doesn't do this before me and detail
the logic as I understand it. I'm going into a meeting right now but I'll
do this when I'm able.

Shirley H. Graver
(End User)
Systems Administrator
Rubber Associates Inc.

-----Original Message-----
From: Dennis Houghton [mailto:dennis@...]
Sent: Wednesday, December 19, 2001 9:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Rb date formula

I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the
week of the year. Their are some dates show up that should be in the
preceding week. I can't see any logical reason for this. I don't exactly
understand how you add/subtract dates and convert to weeks of the year.
Would someone please break this formula down and explain it for me. I don't
understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]




Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=178320.1681224.3270152.1261774/D=egroupweb/S=17050071
83:HM/A=879173/R=0/*http://www.fastweb.com/ib/yahoo-76f>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]
4
I found the following java script that calculates week of the year. Since
this seems to be a regular question maybe some of the VB programmers can
covert this for use under a button.
The following will display the week number of the current date, and can be
adapted to display the week number of any date:
<script type="text/javascript" language="JavaScript"><!--
function y2k(number) { return (number < 1000) ? number + 1900 : number; }

function getWeek(year,month,day) {
var when = new Date(year,month,day);
var newYear = new Date(year,0,1);
var offset = 7 + 1 - newYear.getDay();
if (offset == 8) offset = 1;
var daynum =
((Date.UTC(y2k(year),when.getMonth(),when.getDate(),0,0,0) -
Date.UTC(y2k(year),0,1,0,0,0)) /1000/60/60/24) + 1;
var weeknum = Math.floor((daynum-offset+7)/7);
if (weeknum == 0) {
year--;
var prevNewYear = new Date(year,0,1);
var prevOffset = 7 + 1 - prevNewYear.getDay();
if (prevOffset == 2 || prevOffset == 8) weeknum = 53; else weeknum =
52;
}
return weeknum;
}

var now = new Date();
document.write('Week number = ' +
getWeek(y2k(now.getYear()),now.getMonth(),now.getDate()));
//--></script>

Shirley H. Graver
(End User)
Systems Administrator
Rubber Associates Inc.

-----Original Message-----
From: Dennis Houghton [mailto:dennis@...]
Sent: Wednesday, December 19, 2001 9:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Rb date formula

I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the
week of the year. Their are some dates show up that should be in the
preceding week. I can't see any logical reason for this. I don't exactly
understand how you add/subtract dates and convert to weeks of the year.
Would someone please break this formula down and explain it for me. I don't
understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]




Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=178320.1681224.3270152.1261774/D=egroupweb/S=17050071
83:HM/A=879173/R=0/*http://www.fastweb.com/ib/yahoo-76f>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]
5
Thanks for the fast response.

I think I understand the modulo and truncate, I just can't seem to subtract dates.
Could you show me an example? I'm sorry for being so dense on this.
thanks
----- Original Message -----
From: Shirley Graver
To: vantage@yahoogroups.com
Sent: Wednesday, December 19, 2001 8:53 AM
Subject: RE: [Vantage] Rb date formula


MODULO is defined in report builder as follows:
expression An integer expression (a constant, field name, or
expression whose value is an integer). The expression must be greater than 0
for MODULO to return a correct value.
base A positive integer expression (a constant, field name or expression
whose value is integer) that is the modulo base. For example, angles
measured in degrees use a base of 360 for modulo arithmetic. The result of
the expression "372 MODULO 360" is 12.

Truncate is defined in report builder as follows:
expression A numeric value (a constant, field name, or expression
that results in a numeric value) that you want to truncate.
decimal-places A non-negative integer expression that indicates the number
of decimal places you want to result from truncating the expression.

The formula is truncating to 0 decimal places.

I'll go through the formula, if someone doesn't do this before me and detail
the logic as I understand it. I'm going into a meeting right now but I'll
do this when I'm able.

Shirley H. Graver
(End User)
Systems Administrator
Rubber Associates Inc.

-----Original Message-----
From: Dennis Houghton [mailto:dennis@...]
Sent: Wednesday, December 19, 2001 9:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Rb date formula

I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the
week of the year. Their are some dates show up that should be in the
preceding week. I can't see any logical reason for this. I don't exactly
understand how you add/subtract dates and convert to weeks of the year.
Would someone please break this formula down and explain it for me. I don't
understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]




Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=178320.1681224.3270152.1261774/D=egroupweb/S=17050071
83:HM/A=879173/R=0/*http://www.fastweb.com/ib/yahoo-76f>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]


Yahoo! Groups Sponsor
ADVERTISEMENT




Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]
6
Dennis,
Ok here goes,
In the formula section "IIF((JobHead.ReqDueDate -
DATE("01/01/01")) Modulo 7 =0
The formula is asking if the remainder of 9/11/01 minus 01/01/01
has a remainder of ZERO then use the first formula, if not use the second
formula.

The first formula " 09/01/01 minus 01/01/01 equals 253 days, the 253 days
is then divided by 7 which equals 36 or week 36."

The second formula ), (TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) /
7,0) + 1)) is really the same formula as the first except that this part
"TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0)" is only used if
the remainder is NOT zero. The calculation is then TRUNCATED to zero
decimal places. IE: if (JobHead.ReqDueDate - DATE("01/01/01")) actually
equaled 242.56 days, then the formula would TRIM off the .56 days then add a
WHOLE DAY to the formula. And as I typed that last sentence I saw the error
in the formula. The formula should actually be

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7
=0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE(JobHead.ReqDueDate - DATE("01/01/01"),0) + 1) / 7)

This formula adds a DAY when the calculation is not zero, the other
calculation adds a WEEK when the calculation is not zero.

Maybe this is why your weeks are off dennis.
Shirley H. Graver
(End User)
Systems Administrator
Rubber Associates Inc.

-----Original Message-----
From: Dennis Houghton [mailto:dennis@...]
Sent: Wednesday, December 19, 2001 9:42 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Rb date formula

I would appreciate some help with this formula.

I have a Rb report I created that uses this formula to group jobs due by the
week of the year. Their are some dates show up that should be in the
preceding week. I can't see any logical reason for this. I don't exactly
understand how you add/subtract dates and convert to weeks of the year.
Would someone please break this formula down and explain it for me. I don't
understand what Modulo 7 does, how many places is TRUNCATE, from what end?

IIF((JobHead.ReqDueDate - DATE("01/01/01")) Modulo 7 =
0,((JobHead.ReqDueDate - DATE("01/01/01")) /
7),(TRUNCATE((JobHead.ReqDueDate - DATE("01/01/01")) / 7,0) + 1))

I know this is elementary to a lot of you but I would appreciate any help.
Thanks


[Non-text portions of this message have been removed]




Yahoo! Groups Sponsor
ADVERTISEMENT

<http://rd.yahoo.com/M=178320.1681224.3270152.1261774/D=egroupweb/S=17050071
83:HM/A=879173/R=0/*http://www.fastweb.com/ib/yahoo-76f>


Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .


[Non-text portions of this message have been removed]