Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
spudgun
 
Posts: n/a
Default Calculating number of weeks from two dates


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

  #2   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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

  #3   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 3 Aug 2005 04:17:12 -0500, 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


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
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
Bryan Hessey
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculating number of days (e.g., Mondays) between two dates girlfriend in school Excel Worksheet Functions 10 July 14th 05 07:21 PM
Where is DateDiff function in Excel 2002 ? Nigel Welch Excel Worksheet Functions 4 March 4th 05 04:18 PM
Calculating number of days Carlos Excel Worksheet Functions 3 March 1st 05 09:23 PM
HOW TO CALCULATE NUMBER OF WEEKS BETWEEN TWO GIVEN DATES(MAY BE . WARRENCHERYL Excel Worksheet Functions 1 January 5th 05 09:15 AM
calculating service dates Tanya Excel Worksheet Functions 2 January 3rd 05 09:15 PM


All times are GMT +1. The time now is 11:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"