Calculating number of weeks from two dates

[spudgun]

Hi, I'm both new here and to Excel generally!

I'm running Excel 2003.

I've designed a spreadsheet for my work to calculate holiday taken,
days left etc.
To make my job easier(!) I'm using (attempting to use!) formulae.
I can calculate days & hours taken for a given period but what I would
like to do is calculate the number of weeks from two dates (date leave
started to date leave ended.)

EG: week leave started is 03/01/05 week leave finished is 23/01/05 = 2
weeks.

Is this possible?

Thanks

 

[Bryan Hessey]

The DateDif will do this, assuming the start date is in A1, the end dat
is in A2, and A3 is formatted to Number, no decimal places.

=DateDif(A1,A2+1,"D")

should give you what you need.

 

[Ron Rosenfeld]

Hi, I'm both new here and to Excel generally!

I'm running Excel 2003.

I've designed a spreadsheet for my work to calculate holiday taken,
days left etc.
To make my job easier(!) I'm using (attempting to use!) formulae.
I can calculate days & hours taken for a given period but what I would
like to do is calculate the number of weeks from two dates (date leave
started to date leave ended.)

EG: week leave started is 03/01/05 week leave finished is 23/01/05 = 2
weeks.

Is this possible?

Thanks

Not quite sure how you would come up with your answer.

23 Jan 2005 -- 3 Jan 2005 is 21 days (inclusive) so would be three weeks.

In general, you could use the formula:

=(EndDate - StartDate +1) / 7

to get the number of weeks. Enter the dates in cells and substitute cell
references in the formula (or NAME the cells).

But I don't know how you'd get two weeks from those dates.

Excel stores dates as serial numbers, starting with 1 Jan 1900 or 1 Jan 1904
depending on your date system.


--ron

 

[Dave Peterson]

Just to add to Bryan's reply...

If you're going to use =datedif(), take a look at Chip Pearson's notes at:
http://www.cpearson.com/excel/datedif.htm

 

[Dave Peterson]

But the OP did want the number of weeks, not days.

(But Chip's site is an excellent resouce for =datedif().)

 

[Bryan Hessey]

Your problem in trying to work in Weeks rather than Days is that 5 days
(Monday to Friday) can be 1 week (ie, a week of leave entitlement) or
3/5 weeks (Thursday to Monday) , or 9 days (Saturday to Sunday-week)
can be 1 week.

You would need to use the Weekday function on the first date and asses
whether a week is 5, 6, 7, 8 or 9 days from that point.

A suggestion is that you work in Days and report the 'Leave Remaining'
in the integer of (days / 5) (as weeks) and days.

Otherwise you need to count the number of non-working days that are in
the period (including public holidays) and subtract them from the
Datedif answer.

Sorry I couldn't be more helpful, and thanks Dave for the reminder of
the Chips site, he has some good stuff there. ('stuff' being a
technical term)