Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

YES THAT HELPED THANK YOU. I seem to only half understand the equation so
could you please let me know if the following 3 extra examples are possible?

1. Re-occure every Tuesday
2. Re-occure every other Thursday
3. Re-occure the first Monday (not a specific date) of each Month.

Thank you so much.

"CLR" wrote:

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

This seems to work for "every Tuesday".........

=A1+LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,1,7,6,5, 4,3})

I'll work on the others.......

Vaya con Dios,
Chuck, CABGx3



"Curtis Kitchens" wrote:

YES THAT HELPED THANK YOU. I seem to only half understand the equation so
could you please let me know if the following 3 extra examples are possible?

1. Re-occure every Tuesday
2. Re-occure every other Thursday
3. Re-occure the first Monday (not a specific date) of each Month.

Thank you so much.

"CLR" wrote:

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

Here's a modified formula from an old Leo Heuser post that should work for
the first Monday of each month..........

=DATE(YEAR(A1),MONTH(A1)+1,1)+MOD(10-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2)),7)

Vaya con Dios,
Chuck, CABGx3



"Curtis Kitchens" wrote:

YES THAT HELPED THANK YOU. I seem to only half understand the equation so
could you please let me know if the following 3 extra examples are possible?

1. Re-occure every Tuesday
2. Re-occure every other Thursday
3. Re-occure the first Monday (not a specific date) of each Month.

Thank you so much.

"CLR" wrote:

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

I don't know who you are, but your helpful. Thank you, your equations don't
work 100% so I will spell out with more detail what I need:

1. Your equation for every tuesday works, however what I need is for one
cell to represent the next Tuesday, a second cell to represent 2 tuesdays
from now, a third to represent 3 tuesdays from now, a fourth to represent 4
tuesdays away, and lastly a fifth cell to represent a possible 5th tuesday
away ALL WITHIN this current month. When the month changes to the next month
I need the formula to redo it'self for the 4 (possibly 5) cells to show the
descending tuesdays again.

2. When I entered your "first Monday" of the month equation it gave me the
correct month, but wrong day. For example I got 11/2/06 for the first Monday
of Nov. When in reality it's the 6th. Now I see what's happening because the
2nd was the first monday of this current month (October), but that's not what
I need. See #3.

3. What's really happening is I don't understand the formulas 100%. Where
can I go to learn what "lookup", and {} means, etc..?

4. Lastly The first example (every 15th of the month) works great. Will it
continue to work beyond this year?

Thanks for all your help. Is there a way I can repay you?

Curtis

"CLR" wrote:

Here's a modified formula from an old Leo Heuser post that should work for
the first Monday of each month..........

=DATE(YEAR(A1),MONTH(A1)+1,1)+MOD(10-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2)),7)

Vaya con Dios,
Chuck, CABGx3



"Curtis Kitchens" wrote:

YES THAT HELPED THANK YOU. I seem to only half understand the equation so
could you please let me know if the following 3 extra examples are possible?

1. Re-occure every Tuesday
2. Re-occure every other Thursday
3. Re-occure the first Monday (not a specific date) of each Month.

Thank you so much.

"CLR" wrote:

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,

Hi Curtis....
For the "Tuesdays" formula........assuming original date in A1, and/or all
the way down column A, then put this in B1, and copy down if necessary

=A1+LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,1,7,6,5, 4,3})

And put this in C1 and copy over to G1, and then copy C1:G1 down also if
necessary

=IF(LEN(B1)0,IF(MONTH(B1+7)=MONTH(B1),B1+7,""),"" )

Assuming you had 1/1/06 in A1, then
B1 will return 1/3/06...the next Tuesday
C1 will return 1/10/06...also a Tuesday
D1 will return 1/17/06...also a Tuesday
E1 will return 1/24/06...also a Tuesday
F1 will return 1/31/06...also a Tuesday
G1 will return nothing as the next Tuesday would be in the next month

This would work the same for all the rows below if you had more dates in
column A and copied B1:G1 down.


The formula in B1
=A1+LOOKUP(WEEKDAY(A1),{1,2,3,4,5,6,7},{2,1,7,6,5, 4,3})
reads...to take the date in A1 and add to it a offset number corresponding
to the day of the week of that date...ie if the dayoftheweek of the date in
cell A1 were Sunday (which Excel sees as a 1), then add 2, if it's a Monday
(2) then add 1, if it's a Tuesday(3) then add 7, if it's a Wednesday(4) then
add 6, if it's a Thursday(5) then add 5, if it's a Friday(6) then add 4, and
finally if it's a Saturday(7) then add 3..........this offset gets the next
Tuesday after the date in A1, regardless of which month it's in.

The formula in C1
=IF(LEN(B1)0,IF(MONTH(B1+7)=MONTH(B1),B1+7,""),"" )
reads..........that if the length of the value (date) in B1 is greater than
zero, then if also the Month of the date created by adding 7 to the date in
B1 is equal to the Month of the date in B1(ie: the NEXT Tuesday would be in
the same month as the PREVIOUS Tuesday), then return that Tuesday's date,
otherwise leave the cell blank.

This formula copied over to cell D1 would use cell C1 as the test criteria,
and in E1 would use D1 as the criteria, etc etc......

I wasn't sure exactly what date you wished to appear in what cell, so I
originally gave you a somewhat generic answer, but perhaps this more verbose
explanation will help you to see what's happening in the formulas and let you
put them where you wish, and/or modify them to suit.


=LOOKUP(A1,{1,2,3},{111,222,333})
reads......to look up the value in cell A1 in the first set of brackets, and
replace it with the corresponding term in the second set of brackets....ie:
if A1 contained 1, the corresponding term for 1 in this formula would be 111,
if 2, 222, if 3, 333......

I'll look at the other stuff as time permits, but wanted to get this part to
you ASAP.

hth
Vaya con Dios,
Chuck, CABGx3



"Curtis Kitchens" wrote:

I don't know who you are, but your helpful. Thank you, your equations don't
work 100% so I will spell out with more detail what I need:

1. Your equation for every tuesday works, however what I need is for one
cell to represent the next Tuesday, a second cell to represent 2 tuesdays
from now, a third to represent 3 tuesdays from now, a fourth to represent 4
tuesdays away, and lastly a fifth cell to represent a possible 5th tuesday
away ALL WITHIN this current month. When the month changes to the next month
I need the formula to redo it'self for the 4 (possibly 5) cells to show the
descending tuesdays again.

2. When I entered your "first Monday" of the month equation it gave me the
correct month, but wrong day. For example I got 11/2/06 for the first Monday
of Nov. When in reality it's the 6th. Now I see what's happening because the
2nd was the first monday of this current month (October), but that's not what
I need. See #3.

3. What's really happening is I don't understand the formulas 100%. Where
can I go to learn what "lookup", and {} means, etc..?

4. Lastly The first example (every 15th of the month) works great. Will it
continue to work beyond this year?

Thanks for all your help. Is there a way I can repay you?

Curtis

"CLR" wrote:

Here's a modified formula from an old Leo Heuser post that should work for
the first Monday of each month..........

=DATE(YEAR(A1),MONTH(A1)+1,1)+MOD(10-WEEKDAY(DATE(YEAR(A1),MONTH(A1),2)),7)

Vaya con Dios,
Chuck, CABGx3



"Curtis Kitchens" wrote:

YES THAT HELPED THANK YOU. I seem to only half understand the equation so
could you please let me know if the following 3 extra examples are possible?

1. Re-occure every Tuesday
2. Re-occure every other Thursday
3. Re-occure the first Monday (not a specific date) of each Month.

Thank you so much.

"CLR" wrote:

Maybe something along these lines.........

=IF(DAY(A1)<=15,DATE(YEAR(A1),MONTH(A1),15),DATE(Y EAR(A1),MONTH(A1)+1,15))

hth
Vaya con Dios,
Chuck, CABGx3




"Curtis Kitchens" wrote:

Howdy:

I'd like to add a date into an excel cell that is re-occuring just like you
can in the outlook calander. An example of what I want to accomplish is this:

I'm making a bill pay worksheet for myself, and I want to add "due dates"
into some cells. If a bill is due for example on the 15th of each month then
I want for the date to read 10/15/06 until 10/16/06 when at that point the
cell will repopulate the date to read 11/15/06.

In outlook you have the ability to customize this function in calander to a
high degree. In other words every other Thursday or Every other 1st of the
month, or Every weekday, etc.. If anybody can tell me how to accomplish this
in excel I would appreciate it.

Thank you,