calculate number of days formula
in [Programming]
|
From: Bernd P on 25 Apr 2010 14:46 Hello Rick, Thanks, I like that one! I put your cute and shorter version on my site as well (same page as above). Your version with the optional parameter NWD is not working as expected, though: It returns for 23/08/1933 - 14/10/1933 (NWD is False) 38 days and not 37 as the other versions do, for example. Regards, Bernd On 25 Apr., 19:30, "Rick Rothstein" <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: > Bernd, > > I thought you might be interested in seeing this short one-liner alternative > to your "cwd" function (which requires your "min" function to operate); it > is fully self-contained and, as such, relies only on built-in VB > functions... > > Function CWD(D1 As Date, D2 As Date) As Long > CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ > (Weekday(D2) <> 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6) > End Function > > Note that if you remove the last addend, that is the "+(Weekday(d1, 2)<6)" > logical expression, then the function will return the same results as > Excel's NETWORKDAYS function. If we provide the function with an Optional > parameter, we can make it return either result (yours or NETWORKDAYS's) like > so... > > Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long > CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ > (Weekday(D2) <> 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < 6) > End Function > > The default for the NWD (short for NetWorkDays by the way) parameter is > False, meaning it returns the same results as does your "cwd" function... > pass True in for the NWD parameter and the function returns the same values > as Excel's NETWORKDAYS function. > > -- > Rick (MVP - Excel) > > "Bernd P" <bplumh...(a)gmail.com> wrote in message > > news:f93a41d8-ea2f-4b16-9da9-0ae8dc8c564f(a)k36g2000yqn.googlegroups.com... > > > Hello, > > > If you do no need to take into account holidays, I suggest to use the > > 4th formula shown at > >http://sulprobil.com/html/date_formulas.html > > > Regards, > > Bernd
From: Rick Rothstein on 25 Apr 2010 14:56 It should be noted that in my function, D1 must be the earlier date and D2 the later one (the DateDiff function requires this). Perhaps changing the parameter names would help enforce this requirement... Function CWD(StartDate As Date, EndDate As Date, _ Optional NWD As Boolean) As Long CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _ EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) _ - NWD * (Weekday(StartDate, 2) < 6) End Function -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:ONAsY0J5KHA.1932(a)TK2MSFTNGP05.phx.gbl... > Bernd, > > I thought you might be interested in seeing this short one-liner > alternative to your "cwd" function (which requires your "min" function to > operate); it is fully self-contained and, as such, relies only on built-in > VB functions... > > Function CWD(D1 As Date, D2 As Date) As Long > CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ > (Weekday(D2) <> 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6) > End Function > > Note that if you remove the last addend, that is the "+(Weekday(d1, 2)<6)" > logical expression, then the function will return the same results as > Excel's NETWORKDAYS function. If we provide the function with an Optional > parameter, we can make it return either result (yours or NETWORKDAYS's) > like so... > > Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long > CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ > (Weekday(D2) <> 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < 6) > End Function > > The default for the NWD (short for NetWorkDays by the way) parameter is > False, meaning it returns the same results as does your "cwd" function... > pass True in for the NWD parameter and the function returns the same > values as Excel's NETWORKDAYS function. > > -- > Rick (MVP - Excel) > > > > "Bernd P" <bplumhoff(a)gmail.com> wrote in message > news:f93a41d8-ea2f-4b16-9da9-0ae8dc8c564f(a)k36g2000yqn.googlegroups.com... >> Hello, >> >> If you do no need to take into account holidays, I suggest to use the >> 4th formula shown at >> http://sulprobil.com/html/date_formulas.html >> >> Regards, >> Bernd >
From: Rick Rothstein on 25 Apr 2010 15:15 Sorry, it looks like I implemented the NWD parameter backwards. This should produce the correct results for the NWD parameter equaling True and False... Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2) _ <> 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6) End Function And here it is with the renamed parameters to enforce the requirement that D1 must be the earlier date and D2 the later one... Function CWD(StartDate As Date, EndDate As Date, _ Optional NWD As Boolean) As Long CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _ EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) _ - (Not NWD) * (Weekday(StartDate, 2) < 6) End Function -- Rick (MVP - Excel) "Bernd P" <bplumhoff(a)gmail.com> wrote in message news:dd920ad7-b432-4663-bcc3-3a86f627705d(a)12g2000yqi.googlegroups.com... > Hello Rick, > > Thanks, I like that one! I put your cute and shorter version on my > site as well (same page as above). > > Your version with the optional parameter NWD is not working as > expected, though: > It returns for 23/08/1933 - 14/10/1933 (NWD is False) 38 days and not > 37 as the other versions do, for example. > > Regards, > Bernd > > On 25 Apr., 19:30, "Rick Rothstein" > <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: >> Bernd, >> >> I thought you might be interested in seeing this short one-liner >> alternative >> to your "cwd" function (which requires your "min" function to operate); >> it >> is fully self-contained and, as such, relies only on built-in VB >> functions... >> >> Function CWD(D1 As Date, D2 As Date) As Long >> CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ >> (Weekday(D2) <> 7) + (Weekday(D1) = 1) + (Weekday(D1, 2) < 6) >> End Function >> >> Note that if you remove the last addend, that is the "+(Weekday(d1, >> 2)<6)" >> logical expression, then the function will return the same results as >> Excel's NETWORKDAYS function. If we provide the function with an Optional >> parameter, we can make it return either result (yours or NETWORKDAYS's) >> like >> so... >> >> Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long >> CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - _ >> (Weekday(D2) <> 7) + (Weekday(D1) = 1) - NWD * (Weekday(D1, 2) < >> 6) >> End Function >> >> The default for the NWD (short for NetWorkDays by the way) parameter is >> False, meaning it returns the same results as does your "cwd" function... >> pass True in for the NWD parameter and the function returns the same >> values >> as Excel's NETWORKDAYS function. >> >> -- >> Rick (MVP - Excel) >> >> "Bernd P" <bplumh...(a)gmail.com> wrote in message >> >> news:f93a41d8-ea2f-4b16-9da9-0ae8dc8c564f(a)k36g2000yqn.googlegroups.com... >> >> > Hello, >> >> > If you do no need to take into account holidays, I suggest to use the >> > 4th formula shown at >> >http://sulprobil.com/html/date_formulas.html >> >> > Regards, >> > Bernd >
From: Bernd P on 25 Apr 2010 15:36 Hello Rick, Great. Tested and published as well. No need anymore for my cwd version. Thanks. Regards, Bernd On 25 Apr., 21:15, "Rick Rothstein" <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: > Sorry, it looks like I implemented the NWD parameter backwards. This should > produce the correct results for the NWD parameter equaling True and False.... > > Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long > CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2) _ > <> 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6) > End Function > > And here it is with the renamed parameters to enforce the requirement that > D1 must be the earlier date and D2 the later one... > > Function CWD(StartDate As Date, EndDate As Date, _ > Optional NWD As Boolean) As Long > CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _ > EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) _ > - (Not NWD) * (Weekday(StartDate, 2) < 6) > End Function > > -- > Rick (MVP - Excel)
From: Rick Rothstein on 25 Apr 2010 15:41 You are quite welcome... it was a fun "challenge" even though you didn't present your posting as a challenge. -- Rick (MVP - Excel) "Bernd P" <bplumhoff(a)gmail.com> wrote in message news:bcef26ab-edb3-4674-986b-3df3aef71ab6(a)v14g2000yqb.googlegroups.com... > Hello Rick, > > Great. Tested and published as well. No need anymore for my cwd > version. Thanks. > > Regards, > Bernd > > On 25 Apr., 21:15, "Rick Rothstein" > <rick.newsNO.S...(a)NO.SPAMverizon.net> wrote: >> Sorry, it looks like I implemented the NWD parameter backwards. This >> should >> produce the correct results for the NWD parameter equaling True and >> False... >> >> Function CWD(D1 As Date, D2 As Date, Optional NWD As Boolean) As Long >> CWD = DateDiff("d", D1, D2) - DateDiff("ww", D1, D2) * 2 - (Weekday(D2) >> _ >> <> 7) + (Weekday(D1) = 1) - (Not NWD) * (Weekday(D1, 2) < 6) >> End Function >> >> And here it is with the renamed parameters to enforce the requirement >> that >> D1 must be the earlier date and D2 the later one... >> >> Function CWD(StartDate As Date, EndDate As Date, _ >> Optional NWD As Boolean) As Long >> CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, _ >> EndDate) * 2 - (Weekday(EndDate) <> 7) + (Weekday(StartDate) = 1) >> _ >> - (Not NWD) * (Weekday(StartDate, 2) < 6) >> End Function >> >> -- >> Rick (MVP - Excel)
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: slection change to make the selection go down 1 cell Next: loop VBA request |