Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
hi,
Can someone please help me with a formula to calculate number of days, excluding weekend days? A1 B1 C1 Start date End date Total days (excluding weekend) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
Use the NETWORKDAY Function.
-- Regards Dave Hawley www.ozgrid.com "Ranae" wrote in message ... hi, Can someone please help me with a formula to calculate number of days, excluding weekend days? A1 B1 C1 Start date End date Total days (excluding weekend) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
That looks like a typo (missing S), try
=NETWORKDAYS(start_date,end_date,holidays) Regards, Peter T "ozgrid.com" wrote in message ... Use the NETWORKDAY Function. -- Regards Dave Hawley www.ozgrid.com "Ranae" wrote in message ... hi, Can someone please help me with a formula to calculate number of days, excluding weekend days? A1 B1 C1 Start date End date Total days (excluding weekend) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" wrote in message ... 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" 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" 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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
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" wrote in message ... 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" 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) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
A couple of comments about the text description for your formula solution
and for the functions I posted to you. In the description for your formula, you allude to it being equivalent to NETWORKDAYS, but you forgot to mention that it does not factor in holidays. You also mentioned NETWORKDAYS is from the "analysis add-in"... actually, to be technically correct, it is the "Analysis ToolPak" add-in. I don't know whether you might want to mention that NETWORKDAYS is built into Excel 2007/2010 and is part of the "Analysis ToolPak" add-in for Excel 2003 and earlier. With regard to my functions, the first one (without the NWD parameter) also has the requirement that D1 needs to be the earlier date and D2 the later date. So you may want to use this version of that function instead... Function CWD(StartDate As Date, EndDate As Date) As Long CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 - _ (Weekday(EndDate) < 7) + (Weekday(StartDate) = 1) + (Weekday(StartDate, 2) < 6) End Function As for the presentation of both of my functions, you show them one following the other; however, since they both have the same name, there might be a chance that less experienced VB coders will be confused. Perhaps you could change the lead in description to my function to this... "Rick Rothstein came up with an elegant equivalent VBA version:" where I left the word "elegant" in because you used it, not because I would necessarily have described it that way. Then, you could use something like the following text to lead into the second function... "Rick also provide this more general version which, if the optional NWD parameter is omitted or set to False, will return the same values as the above version of CWD and if the optional NWD parameter is set to True, then it will return the same results as Excel's NETWORKDAYS function (again, without consideration for holidays):" -- Rick (MVP - Excel) "Bernd P" wrote in message ... 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" 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) |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
calculate number of days formula
Hello Rick,
Agreed and changed, thanks. Let us find another "challenge" :-) Regards, Bernd On 25 Apr., 22:10, "Rick Rothstein" wrote: A couple of comments about the text description for your formula solution and for the functions I posted to you. In the description for your formula, you allude to it being equivalent to NETWORKDAYS, but you forgot to mention that it does not factor in holidays. You also mentioned NETWORKDAYS is from the "analysis add-in"... actually, to be technically correct, it is the "Analysis ToolPak" add-in. I don't know whether you might want to mention that NETWORKDAYS is built into Excel 2007/2010 and is part of the "Analysis ToolPak" add-in for Excel 2003 and earlier. With regard to my functions, the first one (without the NWD parameter) also has the requirement that D1 needs to be the earlier date and D2 the later date. So you may want to use this version of that function instead... Function CWD(StartDate As Date, EndDate As Date) As Long * CWD = DateDiff("d", StartDate, EndDate) - DateDiff("ww", StartDate, EndDate) * 2 - _ * * * * (Weekday(EndDate) < 7) + (Weekday(StartDate) = 1) + (Weekday(StartDate, 2) < 6) End Function As for the presentation of both of my functions, you show them one following the other; however, since they both have the same name, there might be a chance that less experienced VB coders will be confused. Perhaps you could change the lead in description to my function to this... "Rick Rothstein came up with an elegant equivalent VBA version:" where I left the word "elegant" in because you used it, not because I would necessarily have described it that way. Then, you could use something like the following text to lead into the second function... "Rick also provide this more general version which, if the optional NWD parameter is omitted or set to False, will return the same values as the above version of CWD and if the optional NWD parameter is set to True, then it will return the same results as Excel's NETWORKDAYS function (again, without consideration for holidays):" -- Rick (MVP - Excel) "Bernd P" wrote in message ... 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" 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate number of days | Excel Worksheet Functions | |||
calculate number of days | Excel Discussion (Misc queries) | |||
calculate number of days | Excel Discussion (Misc queries) | |||
Formula to calculate number of days & ignore blank cells | Excel Discussion (Misc queries) | |||
Formula to calculate number of days between Dates | Excel Worksheet Functions |