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

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

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

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

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