Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Dates advice please
I am seeking to gain a better understanding of constructing formulats using
dates and time. a) In the formula below I enter the start date in Cell A2 b) Number of weeks in B2 C) +/- days offset in C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2)+7*B2+C2) Question 1 How do I modify the above formula to count in months instead of weeks? Question 2 Is it possible to adapt it so I can choose the period to count in: For example: Years, Months, Weeks, Days, Hours, Seconds Any examples would be appreciated Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Dates advice please
Dermot,
To modify your formula to work with months rather than days, use =DATE(YEAR(A2),MONTH(A2)+E2,DAY(A2)) where E2 is the number of months by which the date in A2 is to be offset. To work with days, weeks, or months in the same formula, use something like the following: =DATE(YEAR(A2),MONTH(A2)+((D2="m")*E2),DAY(A2)+((( D2="w")*7*E2))+((D2="d")*E2)) where D2 contains one of (without the quotes) "d", "w", or "m" indicating an offset of days, weeks, or months, and E2 contains the number of units by which to compute the data. For example, if D2 contains 'w' and E2 contains 10, the result of the formula is 10 weeks from A2. I have quite a few pages on my web site that illustrate a great many number of formulas and VBA procedures for working with dates and times. Start at http://www.cpearson.com/excel/datetime.htm . That page has links to many other date-related pages on my web site. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... I am seeking to gain a better understanding of constructing formulats using dates and time. a) In the formula below I enter the start date in Cell A2 b) Number of weeks in B2 C) +/- days offset in C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2)+7*B2+C2) Question 1 How do I modify the above formula to count in months instead of weeks? Question 2 Is it possible to adapt it so I can choose the period to count in: For example: Years, Months, Weeks, Days, Hours, Seconds Any examples would be appreciated Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Dates advice please
Chip
Thanks for thorough explanation and link. If I have any further question when I have had a good look at your link, is it okay to post any questions back here? Cheers Dermot "Chip Pearson" wrote: Dermot, To modify your formula to work with months rather than days, use =DATE(YEAR(A2),MONTH(A2)+E2,DAY(A2)) where E2 is the number of months by which the date in A2 is to be offset. To work with days, weeks, or months in the same formula, use something like the following: =DATE(YEAR(A2),MONTH(A2)+((D2="m")*E2),DAY(A2)+((( D2="w")*7*E2))+((D2="d")*E2)) where D2 contains one of (without the quotes) "d", "w", or "m" indicating an offset of days, weeks, or months, and E2 contains the number of units by which to compute the data. For example, if D2 contains 'w' and E2 contains 10, the result of the formula is 10 weeks from A2. I have quite a few pages on my web site that illustrate a great many number of formulas and VBA procedures for working with dates and times. Start at http://www.cpearson.com/excel/datetime.htm . That page has links to many other date-related pages on my web site. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... I am seeking to gain a better understanding of constructing formulats using dates and time. a) In the formula below I enter the start date in Cell A2 b) Number of weeks in B2 C) +/- days offset in C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2)+7*B2+C2) Question 1 How do I modify the above formula to count in months instead of weeks? Question 2 Is it possible to adapt it so I can choose the period to count in: For example: Years, Months, Weeks, Days, Hours, Seconds Any examples would be appreciated Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Dates advice please
It is absolutely fine to post additional questions here. That's the purpose
of these forums -- peer-to-peer questions and answers. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... Chip Thanks for thorough explanation and link. If I have any further question when I have had a good look at your link, is it okay to post any questions back here? Cheers Dermot "Chip Pearson" wrote: Dermot, To modify your formula to work with months rather than days, use =DATE(YEAR(A2),MONTH(A2)+E2,DAY(A2)) where E2 is the number of months by which the date in A2 is to be offset. To work with days, weeks, or months in the same formula, use something like the following: =DATE(YEAR(A2),MONTH(A2)+((D2="m")*E2),DAY(A2)+((( D2="w")*7*E2))+((D2="d")*E2)) where D2 contains one of (without the quotes) "d", "w", or "m" indicating an offset of days, weeks, or months, and E2 contains the number of units by which to compute the data. For example, if D2 contains 'w' and E2 contains 10, the result of the formula is 10 weeks from A2. I have quite a few pages on my web site that illustrate a great many number of formulas and VBA procedures for working with dates and times. Start at http://www.cpearson.com/excel/datetime.htm . That page has links to many other date-related pages on my web site. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... I am seeking to gain a better understanding of constructing formulats using dates and time. a) In the formula below I enter the start date in Cell A2 b) Number of weeks in B2 C) +/- days offset in C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2)+7*B2+C2) Question 1 How do I modify the above formula to count in months instead of weeks? Question 2 Is it possible to adapt it so I can choose the period to count in: For example: Years, Months, Weeks, Days, Hours, Seconds Any examples would be appreciated Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Dates advice please
Thanks for the reply Chip
I have had a good look at your link wrt dates.....there's plenty to keep me busy for a while.....have a good day. Dermot "Chip Pearson" wrote: It is absolutely fine to post additional questions here. That's the purpose of these forums -- peer-to-peer questions and answers. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... Chip Thanks for thorough explanation and link. If I have any further question when I have had a good look at your link, is it okay to post any questions back here? Cheers Dermot "Chip Pearson" wrote: Dermot, To modify your formula to work with months rather than days, use =DATE(YEAR(A2),MONTH(A2)+E2,DAY(A2)) where E2 is the number of months by which the date in A2 is to be offset. To work with days, weeks, or months in the same formula, use something like the following: =DATE(YEAR(A2),MONTH(A2)+((D2="m")*E2),DAY(A2)+((( D2="w")*7*E2))+((D2="d")*E2)) where D2 contains one of (without the quotes) "d", "w", or "m" indicating an offset of days, weeks, or months, and E2 contains the number of units by which to compute the data. For example, if D2 contains 'w' and E2 contains 10, the result of the formula is 10 weeks from A2. I have quite a few pages on my web site that illustrate a great many number of formulas and VBA procedures for working with dates and times. Start at http://www.cpearson.com/excel/datetime.htm . That page has links to many other date-related pages on my web site. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Dermot" wrote in message ... I am seeking to gain a better understanding of constructing formulats using dates and time. a) In the formula below I enter the start date in Cell A2 b) Number of weeks in B2 C) +/- days offset in C2 =DATE(YEAR(A2),MONTH(A2),DAY(A2)+7*B2+C2) Question 1 How do I modify the above formula to count in months instead of weeks? Question 2 Is it possible to adapt it so I can choose the period to count in: For example: Years, Months, Weeks, Days, Hours, Seconds Any examples would be appreciated Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Any more advice than the Excel help, on the XIRR Function? | Excel Worksheet Functions | |||
Advice on Creating an Excel Formula or Macro - PLEASE HELP!!!! | Excel Worksheet Functions | |||
Excel Application Development Costs - Need Advice | Excel Discussion (Misc queries) | |||
Almost got it !! but need advice | Excel Worksheet Functions | |||
need an advice in excel sheets.... | Excel Discussion (Misc queries) |