Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating number of days (e.g., Mondays) between two dates | Excel Worksheet Functions | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions | |||
Calculating number of days | Excel Worksheet Functions | |||
HOW TO CALCULATE NUMBER OF WEEKS BETWEEN TWO GIVEN DATES(MAY BE . | Excel Worksheet Functions | |||
calculating service dates | Excel Worksheet Functions |