| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
date intervals calculation
I want to set out a table below which works in all circumstances. I am using
Excel 2003: Effective date ? Date Interval Date Days OVERNIGHT ? ? 1 WEEK ? ? 1 MONTH ? ? 3 MONTHS 6 MONTHS 12 MONTHS for instance for 5/ feb 2008 Date Interval Date Days OVERNIGHT 6/2/08 1 1 WEEK 13/2/08 7 1 MONTH 5/3/08 29 3 MONTHS 7/4/08 62 6 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366 So as you can see it should take into account leap years AND must be on the next week day if the date falls at a weekend eg 7/4/08 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
Why is your 1 WEEK entry showing 13/02/2008 - shouldn't it be 12th
Feb? Ans shouldn't 3 months hence be in May? Do the days represent elapsed days or working days? Not sure where 90 comes from. Would you like to re-submit your example? Pete On Feb 5, 3:33*pm, martyn wrote: I want to set out a table below which works in all circumstances. I am using Excel 2003: Effective date * *? Date Interval * Date * *Days OVERNIGHT * * * *? * * * * * * * ? * * * 1 WEEK * * * * *? * * * * * * * *? 1 MONTH * * * * ? * * * * * * * *? 3 MONTHS * * * * * * * * 6 MONTHS * * * * * * * * 12 MONTHS for instance for 5/ feb 2008 Date Interval * Date * *Days OVERNIGHT * * * 6/2/08 *1 1 WEEK * * * * *13/2/08 * * 7 1 MONTH * * * * 5/3/08 * * *29 3 MONTHS * * * * * * * *7/4/08 * * *62 6 MONTHS * * * * * * * *5/8/08 * * *90 12 MONTHS * * * 5/2/09 * *366 So as you can see it should take into account leap years AND must be on the next week day * if the date falls at a weekend eg 7/4/08 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
"Pete_UK" wrote: Why is your 1 WEEK entry showing 13/02/2008 - shouldn't it be 12th Feb? Ans shouldn't 3 months hence be in May? Do the days represent elapsed days or working days? Not sure where 90 comes from. Would you like to re-submit your example? Pete On Feb 5, 3:33 pm, martyn wrote: I want to set out a table below which works in all circumstances. I am using Excel 2003: Effective date ? Date Interval Date Days OVERNIGHT ? ? 1 WEEK ? ? 1 MONTH ? ? 3 MONTHS 6 MONTHS 12 MONTHS for instance for 5/ feb 2008 Date Interval Date Days OVERNIGHT 6/2/08 1 1 WEEK 13/2/08 7 1 MONTH 5/3/08 29 3 MONTHS 7/4/08 62 6 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366 So as you can see it should take into account leap years AND must be on the next week day if the date falls at a weekend eg 7/4/08 Sorry the above should have read : OVERNIGHT 6/2/08 1 1 WEEK 12/2/08 7 1 MONTH 5/3/08 29 2 MONTHS 7/4/08 62 3 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
I'm sure I have over-complicated this, but here goes:
Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23*pm, martyn wrote: Sorry the above should have read : OVERNIGHT * * * 6/2/08 *1 1 WEEK * * * * *12/2/08 * * 7 1 MONTH * * * * 5/3/08 * * *29 2 MONTHS * * * * * * * *7/4/08 * * *62 3 MONTHS * * * * * * * *5/8/08 * * *90 12 MONTHS * * * 5/2/09 * *366 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
yes this works great, have you done this one before? Have checked dats and
works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT 6/2/08 1 1 WEEK 12/2/08 7 1 MONTH 5/3/08 29 2 MONTHS 7/4/08 62 3 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
Glad to hear that, Martyn - thanks for feeding back.
The differences are quite marked if you use 31st Jan 2008 and 1st Feb 2008 as the reference dates in B1. Pete On Feb 6, 8:57*am, martyn wrote: yes *this works great, have you done this one before? Have checked dats and works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT * * * 6/2/08 *1 1 WEEK * * * * *12/2/08 * * 7 1 MONTH * * * * 5/3/08 * * *29 2 MONTHS * * * * * * * *7/4/08 * * *62 3 MONTHS * * * * * * * *5/8/08 * * *90 12 MONTHS * * * 5/2/09 * *366 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
ah, i didnt check that. Mmm oh dear this must work on every date as using
this in a banking application. Is it just 1st of feb in a leap year. Is there a solution? "Pete_UK" wrote: Glad to hear that, Martyn - thanks for feeding back. The differences are quite marked if you use 31st Jan 2008 and 1st Feb 2008 as the reference dates in B1. Pete On Feb 6, 8:57 am, martyn wrote: yes this works great, have you done this one before? Have checked dats and works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT 6/2/08 1 1 WEEK 12/2/08 7 1 MONTH 5/3/08 29 2 MONTHS 7/4/08 62 3 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
As I understood your request, you wanted to add on the appropriate
days/months from the reference date and then adjust to the next working day if the projected date was a weekend. So, these are the values I get with 1st Feb 2008 as the reference date: Date Interval Date Days Day OVERNIGHT 4-Feb-08 3 Mon 1 WEEK 8-Feb-08 7 Fri 1 MONTH 3-Mar-08 31 Mon 2 MONTHS 1-Apr-08 60 Tue 3 MONTHS 1-May-08 90 Thu 12 MONTHS 2-Feb-09 367 Mon As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so this becomes Monday three days hence. One week's advance would be a Friday, whereas one month's advance would be a Saturday, so this becomes the next Monday. Similarly, two and three months in advance are both workdays and so need no adjustment, but one year hence would be a Sunday, so this moves to the next Monday. I thought this is what you wanted, but please advise if it is not. Pete On Feb 6, 10:16*am, martyn wrote: ah, i didnt check that. Mmm oh dear this must work on every date as using this in a banking application. Is it just 1st of feb in a leap year. Is there a solution? "Pete_UK" wrote: Glad to hear that, Martyn - thanks for feeding back. The differences are quite marked if you use 31st Jan 2008 and 1st Feb 2008 as the reference dates in B1. Pete On Feb 6, 8:57 am, martyn wrote: yes *this works great, have you done this one before? Have checked dats and works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT * * * 6/2/08 *1 1 WEEK * * * * *12/2/08 * * 7 1 MONTH * * * * 5/3/08 * * *29 2 MONTHS * * * * * * * *7/4/08 * * *62 3 MONTHS * * * * * * * *5/8/08 * * *90 12 MONTHS * * * 5/2/09 * *366- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
Yes this is perfect thanks, just exactly what I wanted. I misunderstood your
last comment regarding the large differences. I thought you meant you thought there maybe an error on that date and didnt test it myself, but having reveiewed it I understand your orignal comments and agree with the solution. Many thanks Martyn "Pete_UK" wrote: As I understood your request, you wanted to add on the appropriate days/months from the reference date and then adjust to the next working day if the projected date was a weekend. So, these are the values I get with 1st Feb 2008 as the reference date: Date Interval Date Days Day OVERNIGHT 4-Feb-08 3 Mon 1 WEEK 8-Feb-08 7 Fri 1 MONTH 3-Mar-08 31 Mon 2 MONTHS 1-Apr-08 60 Tue 3 MONTHS 1-May-08 90 Thu 12 MONTHS 2-Feb-09 367 Mon As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so this becomes Monday three days hence. One week's advance would be a Friday, whereas one month's advance would be a Saturday, so this becomes the next Monday. Similarly, two and three months in advance are both workdays and so need no adjustment, but one year hence would be a Sunday, so this moves to the next Monday. I thought this is what you wanted, but please advise if it is not. Pete On Feb 6, 10:16 am, martyn wrote: ah, i didnt check that. Mmm oh dear this must work on every date as using this in a banking application. Is it just 1st of feb in a leap year. Is there a solution? "Pete_UK" wrote: Glad to hear that, Martyn - thanks for feeding back. The differences are quite marked if you use 31st Jan 2008 and 1st Feb 2008 as the reference dates in B1. Pete On Feb 6, 8:57 am, martyn wrote: yes this works great, have you done this one before? Have checked dats and works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: =DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: =DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: =DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: =DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: =DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT 6/2/08 1 1 WEEK 12/2/08 7 1 MONTH 5/3/08 29 2 MONTHS 7/4/08 62 3 MONTHS 5/8/08 90 12 MONTHS 5/2/09 366- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
date intervals calculation
Yes, it does do what you wanted - I was just pointing out the
consequences of that particular date, being a Friday and in a Leap Year. Pete On Feb 6, 12:45*pm, martyn wrote: Yes this is perfect thanks, just exactly what I wanted. I misunderstood your last comment regarding the large differences. I thought you meant you thought there maybe an error on that date and didnt test it myself, but having reveiewed it I understand your orignal comments and agree with the solution. * Many thanks Martyn "Pete_UK" wrote: As I understood your request, you wanted to add on the appropriate days/months from the reference date and then adjust to the next working day if the projected date was a weekend. So, these are the values I get with 1st Feb 2008 as the reference date: Date Interval * * Date * * * * * * *Days * * *Day OVERNIGHT * * 4-Feb-08 * * * * *3 * * * * Mon 1 WEEK * * * * * 8-Feb-08 * * * * *7 * * * * Fri 1 MONTH * * * * *3-Mar-08 * * * *31 * * * * Mon 2 MONTHS * * * *1-Apr-08 * * * * 60 * * * *Tue 3 MONTHS * * * *1-May-08 * * * *90 * * * *Thu 12 MONTHS * * *2-Feb-09 * * * 367 * * * *Mon As 1st Feb 2008 is a Friday, then the next day would be a Saturday, so this becomes Monday three days hence. One week's advance would be a Friday, whereas one month's advance would be a Saturday, so this becomes the next Monday. *Similarly, two and three months in advance are both workdays and so need no adjustment, but one year hence would be a Sunday, so this moves to the next Monday. I thought this is what you wanted, but please advise if it is not. Pete On Feb 6, 10:16 am, martyn wrote: ah, i didnt check that. Mmm oh dear this must work on every date as using this in a banking application. Is it just 1st of feb in a leap year. Is there a solution? "Pete_UK" wrote: Glad to hear that, Martyn - thanks for feeding back. The differences are quite marked if you use 31st Jan 2008 and 1st Feb 2008 as the reference dates in B1. Pete On Feb 6, 8:57 am, martyn wrote: yes *this works great, have you done this one before? Have checked dats and works for leap years, If there is a simpler formula I dont care as long as this works thanks "Pete_UK" wrote: I'm sure I have over-complicated this, but here goes: Put your reference date (eg 5/02/08) in B1, and your labels in A3 to A9 and B3:C3 as per your original example, and then put these formula in the cells stated: B4: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+1 ),2)-5),3) B5: *=DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7)+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1),DAY(B$1)+7 ),2)-5),3) B6: *=DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+1,DAY(B$1) ),2)-5),3) B7: *=DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+2,DAY(B$1) ),2)-5),3) B8: *=DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1),MONTH(B$1)+3,DAY(B$1) ),2)-5),3) B9: *=DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1))+MOD(3- MAX(0,WEEKDAY(DATE(YEAR(B$1)+1,MONTH(B$1),DAY(B$1) ),2)-5),3) Format these cells as dates in the style you like, then format cell C4 as a number with 0dp and put this formula in: =B4-B$1 and copy this down into C5:C9. Just change the date in B1 for the table to re-calculate. Hope this helps. Pete On Feb 5, 4:23 pm, martyn wrote: Sorry the above should have read : OVERNIGHT * * * 6/2/08 *1 1 WEEK * * * * *12/2/08 * * 7 1 MONTH * * * * 5/3/08 * * *29 2 MONTHS * * * * * * * *7/4/08 * * *62 3 MONTHS * * * * * * * *5/8/08 * * *90 12 MONTHS * * * 5/2/09 * *366- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUM-ing date for different time intervals | Excel Worksheet Functions | |||
| Looking for a formula to calculate date intervals | Excel Worksheet Functions | |||
| How can I set month/quarter/annual date intervals | Charts and Charting in Excel | |||
| date intervals | Excel Worksheet Functions | |||
| calculating date intervals | Excel Worksheet Functions | |||
Linear Mode
