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


--
spudgun
------------------------------------------------------------------------
spudgun's Profile: http://www.excelforum.com/member.php...o&userid=25862
View this thread: http://www.excelforum.com/showthread...hreadid=392447

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.


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 woul
> like to do is calculate the number of weeks from two dates (date leav
> started to date leave ended.)
>
> EG: week leave started is 03/01/05 week leave finished is 23/01/05 =
> weeks.
>
> Is this possible?
>
> Thank

--
Bryan Hesse
-----------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...fo&userid=2105
View this thread: http://www.excelforum.com/showthread.php?threadid=39244

On Wed, 3 Aug 2005 04:17:12 -0500, spudgun
<(E-Mail Removed)> 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

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

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

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

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