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)
Dave Peterson Wrote:
But the OP did want the number of weeks, not days.
(But Chip's site is an excellent resouce for =datedif().)
Dave Peterson wrote:
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
Bryan Hessey wrote:
The DateDif will do this, assuming the start date is in A1, the end
date
is in A2, and A3 is formatted to Number, no decimal places.
=DateDif(A1,A2+1,"D")
should give you what you need.
spudgun Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=392447
--
Dave Peterson
--
Dave Peterson
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=392447