counting weeks and days between dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pilgrimm@agr.gc.ca - 26 Sep 2007 17:17 GMT

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

Reply to this Message

JW - 26 Sep 2007 17:41 GMT

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
pilgr...@agr.gc.ca 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
[quoted text clipped - 13 lines]
>
> Mel

Reply to this Message

JW - 26 Sep 2007 17:42 GMT

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

> One way:
> Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7
[quoted text clipped - 20 lines]
> >
> > Mel

Reply to this Message

pilgrimm@agr.gc.ca - 26 Sep 2007 17:59 GMT

> Sorry. Left the =" off of the first way.
> ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7
[quoted text clipped - 25 lines]
>
> - Show quoted text -

works great.
thx once again.

Reply to this Message

Tom Ogilvy - 26 Sep 2007 18:36 GMT

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

Signature

Regards,
Tom Ogilvy

> > Sorry. Left the =" off of the first way.
> > ="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7
[quoted text clipped - 28 lines]
> works great.
> thx once again.

Reply to this Message

JW - 26 Sep 2007 18:46 GMT

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"

> 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???
[quoted text clipped - 35 lines]
> > works great.
> > thx once again.

Reply to this Message

Peter T - 26 Sep 2007 20:09 GMT

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

> I know what you mean Tom. I was a little thrown off by his
> description as well.
[quoted text clipped - 42 lines]
> > > works great.
> > > thx once again.

Reply to this Message

Tom Ogilvy - 26 Sep 2007 18:10 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 13 lines]
>
> Mel

Reply to this Message