BAQ formula Blank Title 113749

Yahoo Archive
1
Nikki,

Here is some ABL code I use to figure the necessary Ship Date based on a customer Need By date. The "Number01" field is from the ShipTo table, which when the record is created takes the shipto State or Country and figures the standard shipping time (UPS Ground for US/Canada, whatever value our shipping guy gave me for the rest).

___

if weekday(ttorderrel.needbydate) - ShipTo.Number01 > 1 then ttOrderRel.ReqDate = ttOrderRel.NeedByDate - ShipTo.Number01.
else if weekday(ttorderrel.needbydate) - ShipTo.Number01 > -4 then ttOrderRel.ReqDate = ttOrderRel.NeedByDate - (ShipTo.Number01 + 2).
else if weekday(ttorderrel.needbydate) - ShipTo.Number01 > -9 then ttOrderRel.ReqDate = ttOrderRel.NeedByDate - (ShipTo.Number01 + 4).
else if weekday(ttorderrel.needbydate) - ShipTo.Number01 > -14 then ttOrderRel.ReqDate = ttOrderRel.NeedByDate - (ShipTo.Number01 + 6).
else if weekday(ttorderrel.needbydate) - ShipTo.Number01 > -19 then ttOrderRel.ReqDate = ttOrderRel.NeedByDate - (ShipTo.Number01 + 8).


___

The "weekday" function in ABL returns a value of 1 (Sunday) through 7 (Saturday). Crystal's "weekday" function acts the same by default, but it gives you the option to change what day the week starts on (check the Help for syntax). Excel has a similar function but I believe it returns a 0 (Sunday) through 6 (Saturday).

From there it's just math.

Good luck!

Ernie Lowell
Diba Industries

--- In vantage@yahoogroups.com, "nikki_vesely" <nikki.vesely@...> wrote:
>
> I would like to count the days between 2 dates with out counting Saturday or Sunday. What is the best way to do this? Does anyone already have a formula?
>
2
I would like to count the days between 2 dates with out counting Saturday or Sunday. What is the best way to do this? Does anyone already have a formula?
3
Hi Nikki,

Just curious, where you will use/display the result.
Is the value needed for a report or form?



--- In vantage@yahoogroups.com, "nikki_vesely" <nikki.vesely@...> wrote:
>
> I would like to count the days between 2 dates with out counting Saturday or Sunday. What is the best way to do this? Does anyone already have a formula?
>
4
I had intended to use it in a dashboard. The only suggestion I have received is to dump into Excel and use the =networkdays formula.
Thanks

--- In vantage@yahoogroups.com, "b_ordway" <cooner_55421@...> wrote:
>
> Hi Nikki,
>
> Just curious, where you will use/display the result.
> Is the value needed for a report or form?
>
>
>
> --- In vantage@yahoogroups.com, "nikki_vesely" <nikki.vesely@> wrote:
> >
> > I would like to count the days between 2 dates with out counting Saturday or Sunday. What is the best way to do this? Does anyone already have a formula?
> >
>
5
This can be easily accomplished in Crystal.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
nikki_vesely
Sent: Thursday, September 20, 2012 10:34 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: BAQ formula





I had intended to use it in a dashboard. The only suggestion I have received
is to dump into Excel and use the =networkdays formula.
Thanks

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"b_ordway" <cooner_55421@...> wrote:
>
> Hi Nikki,
>
> Just curious, where you will use/display the result.
> Is the value needed for a report or form?
>
>
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"nikki_vesely" <nikki.vesely@> wrote:
> >
> > I would like to count the days between 2 dates with out counting
Saturday or Sunday. What is the best way to do this? Does anyone already
have a formula?
> >
>



No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2441/5281 - Release Date: 09/20/12




-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2221 / Virus Database: 2441/5281 - Release Date: 09/20/12

[Non-text portions of this message have been removed]
6
A rough formula would be to subtract the two dates divide by 7 using integer division or use truncate to drop the decimals. This gives you the whole weeks between the two dates. Then multiply by 5 to get the days for the whole weeks. The final piece is calculating what partial week we might have left. This would involve checking what day of the week it is. For example if today is Tuesday we would have two works days in the current week.

Here is function in basic that you should be able to rework into ABL, I can't vouch for it validity but it looks to be doing what is needed.

' WorkDays
' returns the number of working days between two dates
Public Function WorkDays(ByVal dtBegin As Date, ByVal dtEnd As Date) As Long

Dim dtFirstSunday As Date
Dim dtLastSaturday As Date
Dim lngWorkDays As Long

' get first sunday in range
dtFirstSunday = dtBegin + ((8 - Weekday(dtBegin)) Mod 7)

' get last saturday in range
dtLastSaturday = dtEnd - (Weekday(dtEnd) Mod 7)

' get work days between first sunday and last saturday
lngWorkDays = (((dtLastSaturday - dtFirstSunday) + 1) / 7) * 5

' if first sunday is not begin date
If dtFirstSunday <> dtBegin Then

' assume first sunday is after begin date
' add workdays from begin date to first sunday
lngWorkDays = lngWorkDays + (7 - Weekday(dtBegin))

End If

' if last saturday is not end date
If dtLastSaturday <> dtEnd Then

' assume last saturday is before end date
' add workdays from last saturday to end date
lngWorkDays = lngWorkDays + (Weekday(dtEnd) - 1)

End If

' return working days
WorkDays = lngWorkDays

End Function

Jim Kinneman
Encompass Solutions, Inc

--- In vantage@yahoogroups.com, "nikki_vesely" <nikki.vesely@...> wrote:
>
> I would like to count the days between 2 dates with out counting Saturday or Sunday. What is the best way to do this? Does anyone already have a formula?
>