counting weeks and days between dates

  • Thread starter Thread starter pilgrimm
  • Start date Start date
P

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
 
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
 
Sorry. Left the =" off of the first way.
="Weeks: "&INT((C3-B3)/7)&" Days: "&(C3-B3)-INT((C3-B3)/7)*7
 
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.
 
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???
 
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"
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating calendar days and completion dates. 4
Spreading date range across days in weeks (advanced) 7
Count days between two dates excluding weekends 1
Day of Week from dd/mm/yyyy 7
Formula to tell day of week 2
Excel Date Count 2
Weeks between two Dates 3
Excel Formula - Count Dates from another Sheet 8

Back
Top