calculating (correctly) the weeks and days between dates

[neon]

Greetings,

I need to create a formula that returns the number of weeks and day
between two dates. I thought this sounded simple, but I understand tha
excel 2002 (which is the version that I am using) calculates dates o
the assumption that there are 12, 30-day months in a year. Presumabl
this 'bogs' things up when you have dates that cross a month with mor
or less than 30 days in it???????

come on, give us a clue

All help appreciated

neo

 

[JE McGimpsey]

One way:

Since dates are just integer offsets from a base date, you can use
regular math:


A1: start date
A2: end date

A3: ="There_are_" & INT((A2-A1)/7) & "_weeks_and_" & MOD(A2-A1,7) &
"_days between_the_dates."

where I've used underscores to represent spaces to prevent unfortunate
line breaks.

There's nothing special about XL02.

 

[Juan Sanchez]

NEON

This formula will give you a result like this "5 Weeks, 3
Days"

=INT(DATEDIF(C15,C16,"d")/7) & " Weeks, " & DATEDIF
(C15,C16,"d")-INT(DATEDIF(C15,C16,"d")/7)*7 & " Days"

If instead you just want a decimal number for weeks, use:

=DATEDIF(C15,C16,"d")/7

This will give you a 5.32 Type result.

C15 Is the start day and c16 the end date...

Cheers
Juan

 

[Norman Harker]

Hi neon!

Or another way that preserves the number capabilities of the result:

=(B1-A1)/7
Format # ?/7

 

[neon]

Thank you very much Norman, Juan and J E M...
I managed to tailor your suggestions to provide exactly what I wa
after... A virtual beer to all of you :