counting weeks and days between dates

[pilgrimm]

I need a formula that will give me the total number of weeks and days
between 2 dates. ie. b3=Apr-2,2001, c3=Oct.31,2001 what is the
total number of weeks and days between these dates.

I have the formula to get the number of weeks but have trouble getting
the number of days left to show.

I have so far: =INT((C3-B3)/7) to get the weeks.

When I count out the numbers, I get 3 calendar days left. But if I
simply use the total number of days between B3 and C3 less number of
weeks times 7 I get 2 days. It is out 1 day.

Any ideas on how to make it more accurate?
thx all

Mel

 

[JW]

One way:
Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7

Another way:
="Weeks: "&INT(DATEDIF(B3,C3,"d")/7)&" Days: "&DATEDIF(B3,C3,"d")-
INT((C3-B3)/7)*7

 

[JW]

Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7

 

[pilgrimm]

Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7





- Show quoted text -

works great.
thx once again.

 

[Guest]

That is because when you subtract the start day from the end day, you don't
count the start day. you need to do your calculations with

=INT((C3-B3+1)/7)

=mod((c3-B3+1),7)

that give me 30 weeks and 3 days.

 

[Guest]

For your sample, both JW's suggestions gave me 30 weeks and 2 days - I
thought you said that was your problem - you wanted 3 days. Go figure???

 

[JW]

I know what you mean Tom. I was a little thrown off by his
description as well.
I was just looking around and came across a page on Chip's site where
he has a pretty slick method of doing this. His method also worked
out to 30 weeks 2 days.
=TRUNC((C3-B3)/7)&" Weeks "&MOD(C3-B3,7)&" Days"

 

[Peter T]

If your first date is the 1st of the month and your second date the 3rd,
that's 3 days if you count each of them but the difference between the dates
is of course 2.

If you want inclusive days, which it seems you do, add 1 to their
difference.

Consider two dates that fall on consecutive Mondays, for your purposes is
that a week or eight days.

Regards,
Peter T