Formula for Days Late

Yahoo Archive
1
Specific to the BAQ, this should work, or get you close.

I would try (TRUNCATE((date1 - date2)/7,0)*5) + ((date1 - date2) MODULO 7).

Logic is give the integer number of weeks late * 5 days in a week, + the
remainder after dividing the latedays by 7.
It won't be exact, but it should be close anyway.

Good luck,
Aaron Hoyt
Vantage Plastics


-----Original Message-----
From: bbelzer42 [mailto:bbelzer@...]
Sent: Wednesday, January 05, 2005 5:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Formula for Days Late




I have a Query showing days late on our jobs. The problem is that
it includes the weekends. I would like to see days late based on a
regular workweek Mon-Fri. Thus reducing a Monday being 3 days late
to 1 day if it was due Friday.

Does anyone know how to code this?

Thanks!
Ben





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
Yahoo! Groups Links
2
I have a Query showing days late on our jobs. The problem is that
it includes the weekends. I would like to see days late based on a
regular workweek Mon-Fri. Thus reducing a Monday being 3 days late
to 1 day if it was due Friday.

Does anyone know how to code this?

Thanks!
Ben
3
Crystal or report builder?

Frank Zeigafuse
Innovative Office Products
General Manager
Direct Phone: 610-559-6369
Email: fzeigafuse@...



-----Original Message-----
From: bbelzer42 [mailto:bbelzer@...]
Sent: Wednesday, January 05, 2005 5:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Formula for Days Late




I have a Query showing days late on our jobs. The problem is that
it includes the weekends. I would like to see days late based on a
regular workweek Mon-Fri. Thus reducing a Monday being 3 days late
to 1 day if it was due Friday.

Does anyone know how to code this?

Thanks!
Ben





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
Yahoo! Groups Links










<www.LCDarms.com> -- Ergonomic mounting solutions for monitors and keyboards

This e-mail is from Innovative Office Products, Inc. but expresses the views of the sender and not necessarily the views of the company. The email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee, nor may it be copied in any way. If received in error, please delete and notify the sender via e-mail or sales@... quoting the name of the sender. Messages sent via this medium may be subject to delays, non-delivery and unauthorized alteration. This e-mail has been prepared using information believed by the author to be reliable and accurate. Unless expressly stated otherwise, this e-mail does not form part of a legally binding contract or agreement between recipient and Innovative Office Products, Inc.

Warning: Although Innovative Office Products, Inc. has taken reasonable precautions to ensure no viruses are present in this email, we cannot accept responsibility for any loss or damage arising from the use of this email or attachments. It is, therefore, your responsibility to scan attachments (if any).
4
Actually I am using Business Activity Query. If you cant help there
can you help with Crystal?

THanks!

--- In vantage@yahoogroups.com, "Frank Zeigafuse" <fzeigafuse@l...>
wrote:
> Crystal or report builder?
>
> Frank Zeigafuse
> Innovative Office Products
> General Manager
> Direct Phone: 610-559-6369
> Email: fzeigafuse@L...
>
>
>
> -----Original Message-----
> From: bbelzer42 [mailto:bbelzer@t...]
> Sent: Wednesday, January 05, 2005 5:17 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Formula for Days Late
>
>
>
>
> I have a Query showing days late on our jobs. The problem is that
> it includes the weekends. I would like to see days late based on
a
> regular workweek Mon-Fri. Thus reducing a Monday being 3 days
late
> to 1 day if it was due Friday.
>
> Does anyone know how to code this?
>
> Thanks!
> Ben
>
>
>
>
>
> 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
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
> <www.LCDarms.com> -- Ergonomic mounting solutions for monitors
and keyboards
>
> This e-mail is from Innovative Office Products, Inc. but expresses
the views of the sender and not necessarily the views of the
company. The email and any files transmitted with it are
confidential to the intended recipient at the e-mail address to
which it has been addressed. It may not be disclosed or used by any
other than that addressee, nor may it be copied in any way. If
received in error, please delete and notify the sender via e-mail or
sales@L... quoting the name of the sender. Messages sent via this
medium may be subject to delays, non-delivery and unauthorized
alteration. This e-mail has been prepared using information believed
by the author to be reliable and accurate. Unless expressly stated
otherwise, this e-mail does not form part of a legally binding
contract or agreement between recipient and Innovative Office
Products, Inc.
>
> Warning: Although Innovative Office Products, Inc. has taken
reasonable precautions to ensure no viruses are present in this
email, we cannot accept responsibility for any loss or damage
arising from the use of this email or attachments. It is, therefore,
your responsibility to scan attachments (if any).
5
Yes, the f(x) is called day of week

DayOfWeek({Your DB Field}) in 2 to 6)

I am pretty sure day 2 to 6 is for Monday through Friday.

Crystal help on this function is pretty good. Hope this helps.

I used on my own scheduled shipments report to just show during Monday -
friday

Frank Zeigafuse
Innovative Office Products
General Manager
Direct Phone: 610-559-6369
Email: fzeigafuse@...



-----Original Message-----
From: bbelzer42 [mailto:bbelzer@...]
Sent: Thursday, January 06, 2005 11:10 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Formula for Days Late




Actually I am using Business Activity Query. If you cant help there
can you help with Crystal?

THanks!

--- In vantage@yahoogroups.com, "Frank Zeigafuse" <fzeigafuse@l...>
wrote:
> Crystal or report builder?
>
> Frank Zeigafuse
> Innovative Office Products
> General Manager
> Direct Phone: 610-559-6369
> Email: fzeigafuse@L...
>
>
>
> -----Original Message-----
> From: bbelzer42 [mailto:bbelzer@t...]
> Sent: Wednesday, January 05, 2005 5:17 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Formula for Days Late
>
>
>
>
> I have a Query showing days late on our jobs. The problem is that
> it includes the weekends. I would like to see days late based on
a
> regular workweek Mon-Fri. Thus reducing a Monday being 3 days
late
> to 1 day if it was due Friday.
>
> Does anyone know how to code this?
>
> Thanks!
> Ben
>
>
>
>
>
> 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
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
> <www.LCDarms.com> -- Ergonomic mounting solutions for monitors
and keyboards
>
> This e-mail is from Innovative Office Products, Inc. but expresses
the views of the sender and not necessarily the views of the
company. The email and any files transmitted with it are
confidential to the intended recipient at the e-mail address to
which it has been addressed. It may not be disclosed or used by any
other than that addressee, nor may it be copied in any way. If
received in error, please delete and notify the sender via e-mail or
sales@L... quoting the name of the sender. Messages sent via this
medium may be subject to delays, non-delivery and unauthorized
alteration. This e-mail has been prepared using information believed
by the author to be reliable and accurate. Unless expressly stated
otherwise, this e-mail does not form part of a legally binding
contract or agreement between recipient and Innovative Office
Products, Inc.
>
> Warning: Although Innovative Office Products, Inc. has taken
reasonable precautions to ensure no viruses are present in this
email, we cannot accept responsibility for any loss or damage
arising from the use of this email or attachments. It is, therefore,
your responsibility to scan attachments (if any).





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
Yahoo! Groups Links










<www.LCDarms.com> -- Ergonomic mounting solutions for monitors and keyboards

This e-mail is from Innovative Office Products, Inc. but expresses the views of the sender and not necessarily the views of the company. The email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee, nor may it be copied in any way. If received in error, please delete and notify the sender via e-mail or sales@... quoting the name of the sender. Messages sent via this medium may be subject to delays, non-delivery and unauthorized alteration. This e-mail has been prepared using information believed by the author to be reliable and accurate. Unless expressly stated otherwise, this e-mail does not form part of a legally binding contract or agreement between recipient and Innovative Office Products, Inc.

Warning: Although Innovative Office Products, Inc. has taken reasonable precautions to ensure no viruses are present in this email, we cannot accept responsibility for any loss or damage arising from the use of this email or attachments. It is, therefore, your responsibility to scan attachments (if any).
6
If you are doing your report in Access here is a possible solution:

Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function


Butch

________________________________

From: bbelzer42 [mailto:bbelzer@...]
Sent: Wednesday, January 05, 2005 2:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Formula for Days Late



I have a Query showing days late on our jobs. The problem is that
it includes the weekends. I would like to see days late based on a
regular workweek Mon-Fri. Thus reducing a Monday being 3 days late
to 1 day if it was due Friday.

Does anyone know how to code this?

Thanks!
Ben





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



________________________________

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

* 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]
7
There is a function available for Crystal 8.5 and below. Search the Business
Objects KB for the following article: cr_business_days_hours.zip
I have not tried this function, but the zip file contains a 52 page how to
article.

Have fun!!

Dan Shallbetter
States Electric Mfg.

-----Original Message-----
From: Butch Menge [mailto:bmenge@...]
Sent: Thursday, January 06, 2005 10:37 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Formula for Days Late


If you are doing your report in Access here is a possible solution:

Public Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case WeekDay(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case WeekDay(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + WeekDay(EndDate) - WeekDay(BegDate)
End If
End Function


Butch

________________________________

From: bbelzer42 [mailto:bbelzer@...]
Sent: Wednesday, January 05, 2005 2:17 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Formula for Days Late



I have a Query showing days late on our jobs. The problem is that
it includes the weekends. I would like to see days late based on a
regular workweek Mon-Fri. Thus reducing a Monday being 3 days late
to 1 day if it was due Friday.

Does anyone know how to code this?

Thanks!
Ben





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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>



________________________________

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
<http://groups.yahoo.com/group/vantage/>

* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>

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




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



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/.
<http://groups.yahoo.com/group/vantage/files/.>
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
<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
<http://groups.yahoo.com/group/vantage/links>



_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
<http://groups.yahoo.com/group/vantage/>


* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com
<mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* 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]