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
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.
Originally Posted by spudgun
On Wed, 3 Aug 2005 04:17:12 -0500, spudgun
<spudgun.1t6nqb_1123063507.4327@excelforum-nospam.com> 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)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks