|
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com