Number of weeks from dates (1 Viewer)

[BobNTN]

Is there a way to calculate number of weeks from a begin date and end date ?
Example. Cell D5 has a begin date and cell E5 has an end date. In cell G20, I would like to show the number of weeks (and left over days) from those dates if that is possible.

 

[smig]

Look at datediff()

 

[BobNTN]

Appears datediff will only do days, months, years.
Looks like they didn't see fit to build in weeks to the possibilities.

Thanks

 

[RayH]

This should give what you need.

in G20: (weeks)
=INT(ABS((E5-D5))/7)

in G21: (days remaining)
=INT(((ABS(E5-D5))/7-G20)*7)

cannot combine the two as it will cause circular reference (the days calculation uses the week calculation).

if decimal places are ok in the weeks calculation remove the INT function
The ABS turns negative values to positive ones. This way if the begin date is AFTER the BEGIN you will still get a positive difference.

*edit: datediff() is not available in Excel

 

[BobNTN]

Thanks.
That works great.

 

[smig]

The similar function in excel will be days360()
Ask MS why use different functions

 

[Brianwarnock]

Days360 is based on a 360 day year???
Datedif (NOTE 1 f) still works but there has been no help for it since about EXCEL 97, it also does not calculate weeks.

the question I would ask the poster was does he mean complete calendar weeks and if yes starting on which day, or just blocks of 7 days as per the calculation . They are not the same, the former can have spare days greater than 7.

Brian