I've been having problems coming up with a formula that will take a start date and an end date and come up with the number of weeks INVOLVED within this date range (each week being a Sunday through Saturday). My problems is that the start and end dates could be any day of the week and not necessarily the same day (meaning divisible by 7 doesn't always work). I tried using ROUND((A2-A1)/7,0)+1 where A1 was the start date...and A2 was the end date. The problem I had was if I picked a Monday as the start date, and went 12 days out (The saturday of week2)...it came up stating 3 weeks were involved - AND if I selected a Friday start date and picked the following monday in week 2 - the result was 1 week involved when 2 different weeks were involved.
My brain is fried from data entry and I'm stuck. Any suggestions or beer would be greatly appreciated! Thank you in advance.
Last edited by GuruWannaB; 11-03-2008 at 05:10 PM.
Try this:
Does that work for you?=CEILING((A2-A1+1)/7,1)
I tried this...however it didnt work when I used the starting date of 11/7/2008(friday of week 1) and used the ending date of 11/10/2008 (Monday of week 2). Althought there is only a span of 4 days, they involve two seperate calendar weeks - so the output should have been 2 - when it came out as only 1.
If weeks are Sunday to Saturday then this formula counts how many weeks the date range A1 to A2 intersects
=INT((A2-WEEKDAY(A2)-A1)/7)+2
Woot! Thanks...that worked perfectly! Thank you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks