counting weeks and days between dates
 

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

counting weeks and days between dates
 

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
wrote:
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

counting weeks and days between dates
 

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

JW wrote:
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
wrote:
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

counting weeks and days between dates
 

On Sep 26, 11:42 am, JW wrote:
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7



JW wrote:
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
wrote:
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- Hide quoted text -

- Show quoted text -

works great.
thx once again.

counting weeks and days between dates
 

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.

--
Regards,
Tom Ogilvy


" wrote:

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

counting weeks and days between dates
 

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???

--
Regards,
Tom Ogilvy


" wrote:

On Sep 26, 11:42 am, JW wrote:
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7



JW wrote:
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
wrote:
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- Hide quoted text -

- Show quoted text -

works great.
thx once again.

counting weeks and days between dates
 

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"

Tom Ogilvy wrote:
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???

--
Regards,
Tom Ogilvy


" wrote:

On Sep 26, 11:42 am, JW wrote:
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7



JW wrote:
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
wrote:
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- Hide quoted text -

- Show quoted text -

works great.
thx once again.

counting weeks and days between dates
 

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


"JW" wrote in message
ups.com...
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"

Tom Ogilvy wrote:
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???

--
Regards,
Tom Ogilvy


" wrote:

On Sep 26, 11:42 am, JW wrote:
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7



JW wrote:
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
wrote:
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- Hide quoted text -

- Show quoted text -

works great.
thx once again.