How many Mondays
I need to find a way to calculate the number of monday between two dates.
It would depend upon whether you wanted inclusive, for instance, Date1 is 12/7/2009 and Date2 is 12/21/2009 and you start counting forward on 12/8, you would have 2 Mondays. But if you count the 7th (inclusive), it would be 3 Mondays.
Exclusive: Div ( date2 - ( Date1 - Mod ( Date1 - 1 ; 7 ) ) ; 7 )
Inclusive: Div ( date2 - ( Date1 - Mod ( Date1 - 1 ; 7 ) ) ; 7 ) + ( DayOfWeek ( Date1 ) = 2 )
I believe there are prettier calcs around but this is what came to me this early morning. :smileyhappy:
UPDATE: Yeah, no doubt Comment's calc would be more elegant.
The "inclusive" worked. If you don´t mind can you explain the logic on the Mod part? "Mod ( Date1 - 1 ; 7 )"
Mod() per FM Help … Returns the remainder after number is divided by divisor.
Dates are numbers of days since 1/1/0001 (which was a Monday ). It is more difficult to move forward/backward using DayOfWeek() because, if you go backwards through the days, it doesn't switch from 2 to 1 to 7. Mod() allows jump (and span) through week boundaries.
Mod() tells us the left-over days after the division by 7 on any date eliminates all full weeks. I wanted to jump back to the prior Monday always to begin counting forward. I couldn't use Mod ( start ; 7 ) + 1 instead of Mod ( start – 1 ; 7 ) because that would have jumped me forward if the start day was Sunday as would DayOfWeek ( start ) + 2 or even DayOfWeek ( start - 1 ) + 8. I could have also used DayOfWeek ( start - 1 ) + 1 but just tend to focus on using Mod() more than DayOfWeek().
So, after dropping back to prior Monday, I subtract the difference from the end date and divide by 7 to give total number of full weeks (Mondays). I am not a DateMaster; I just enjoy working with dates.
Retrieving data ...