is there a way to calculate the number of days in a month with sunday and saturday not included ?
Here is one way, given that you provide the start and end date of the month:
If ( Date1 and Date2 ; Let ( [ spanStart = Date1 - Mod ( Date1 ; 7 ) ; spanEnd = Date2 - Mod ( Date2 ; 7 ) + 6 ; wkndCount = Div ( spanEnd - spanStart + 1 ; 7 ) * 2 - (spanStart < Date1 ) - ( spanEnd > Date2 ) ] ; Date2 - Date1 + 1 - wkndCount ) ) The span starts at the first Sunday prior to the start date and the span ends on Saturday after the end date. Weekend count is the full weeks in between then boolean test deducts one day if span starts before Date1 (indicating first day of span is Sunday and NOT within the range) and deducts one day if span ends after end date (indicating last day of span is Saturday and not within the range). Once we know the Weekend count, we subtract Weekend count from total days in the (inclusive) date range.
To allow User to enter any date and use this concept, create a field called gDate (global date). The User can type any date into this field (it doesn't have to be the first of the month - any date will work) and the following calculation will take it from there:
If ( gDate ;Let ( [start = gDate - Day ( gDate ) + 1 ;end = Date ( Month ( gDate ) + 1 ; 0 ; Year ( gDate ) ) ;spanStart = start - Mod ( start ; 7 ) ;spanEnd = end - Mod ( end ; 7 ) + 6 ;wkndCount = Div ( spanEnd - spanStart + 1 ; 7 ) * 2 - (spanStart < start ) - ( spanEnd > end )] ;end - start + 1 - wkndCount))
thx man for the help,
in the first script, "Date1" and "Date2", are they counted or i have to adjust the function cuz i keep messing 2 days in my count
I'm not sure what you mean. Date1 would be the first of the month (in your situation) and Date2 would be the last day of the month. How are you coming up short? Can you give me the dates you are plugging in? Have you tried the second approach instead?
date1 = 02/12/2010 and date2 = 20/12/2010
and if its possible, could you xplain ur script to me, what spanStart and spanEnd refers to?
i used 21/03/2011 as an example and (MOD(Date1;7) returns "1" and the DIV returns 104888 days.
i guess to counted the number of day from a random date to Date1 and Date2 and then u took the difference and u divided it on 7 to return the number of weeks and then *2 to return the number of sundays/saturdays.
i'm having a rugh time understandeing the " - (spanStart < Date1 ) - ( spanEnd > Date2 ) " !!
i would really apreciate a lil more xplanation :)
Thnx in advance
Well, if I plug 12/2/2010 (your 02/12/2010) into Date1 and 12/20/2010 (your 20/12/2010) into the calculation, it produces 13 days which is the correct number of workdays (Monday - Friday) in that span including Dec 2nd and Dec 20th which are both work days. The way to verify is to actually open your computer calendar and manually count them. Leave your calendar open as you play with these calculations and test the results.
Dates are actually number of days since 1/1/0001. So when you divide by 7, you have the number of full weeks since 1/1/0001. And Mod() tells you the number of days left over which actually points to the day of week. The day of week of 1/1/0001 is actually Monday (and your example date is Monday 21/3/2011 so subtracting Mod( date ; 7 ) from the date actually backs the date up to Sunday 20/3/2011. But, using your date 21/3/2011, date - Mod ( date - 1 ; 7 ) equals Monday which is same ... so the date result would be 3/20/2011 <- typo ... 3/21/2011 (it would not go backwards at all).
This portion: Div ( spanEnd - spanStart + 1 ; 7 )* 2 - (spanStart < date1 ) - ( spanEnd > date2 )
Portion in blue tells us the number of 'complete' weeks in this complete span including Sunday prior and Saturday after (then times twice so we account for two weekend days. The portion IN RED is a Boolean test. It is like writing If ( spanStart < date1 ; 1 ; 0 ) but Boolean doesn't require the If(). It is necessary to wrap with parenthesis here to change the order of operations. <-- I changed this paragraph for clarify. So the portion of the calculation in red says:
Subtract 1 (from the portion in blue) if spanStart is less than starting date and subtract another 1 (from the portion in blue) if spanEnd is greater than the ending date. What you are doing is very good ... you are pulling the calculation apart to understand it. This calculation (as with all good and true date calculations) will properly adjust for leap year and they will never fail.
Date calculations are things of beauty and, as I was told once by the greatest date calculation Master ever, Michael Horak (aka Comment), "Know thy Mod() function, when thou cometh to date calculations."
By the way, in your first response you said, "...in the first script..." I just wanted to mention that it is not a script but rather a calculation (result is number). It CAN be used in a calculation within a script however. Also, if you use Get ( CurrentDate ) as one of these start or end dates, you must change the calculation by checking 'unstored' in Storage Options so it will update every time the calculation is accessed.
got it :)
Retrieving data ...