Update a date into the same cell?
 

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A2))-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

That looks promising. How would it work for a leap year though?

Thanks a lot

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A2 ))-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A2 ))-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
...
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A2) )-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
.. .
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A2 ))-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

OK, I think I've got it and after I thought about it, I don't think
the leap year matters. It's 364 days no matter what.

=MOD(DATE(2009,2,26)-TODAY(),364)+TODAY()

Anyone think this will not work? It seems to so far.

Thanks for all the help :)

On Sun, 28 Jun 2009 18:35:35 -0500, Greg L
wrote:

Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
. ..
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A 2))-1)

Otherwise, if you want to update the cell based on the actual cell, you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

Greg L wrote:
OK, I think I've got it and after I thought about it, I don't think
the leap year matters. It's 364 days no matter what.

=MOD(DATE(2009,2,26)-TODAY(),364)+TODAY()

Anyone think this will not work? It seems to so far.

Thanks for all the help :)


You are correct, sir, on all accounts. Especially the observation that
the rule is 364 day increments regardless of leap days.

While you were working, I was also working on these ridiculous (but
effective formulae). I share them with some shame for having overlooked
the simple solution:

Looking forward five years or so:
=364*MATCH(A2-1,{0,1,2,3,4,5}*364+$E$1,1)+$E$1

{array formula, more generalized:}
=364*MATCH(A2-1,(ROW(INDIRECT("1:99"))-1)*364+$E$1,1)+$E$1

where A2 has "today" and E1 has the paydate to check.

Yet your solution is superior, for being simpler, and that it does not
require knowledge of an older paydate relative to today.

Good job!

Update a date into the same cell?
 

You're right, it's 364 no matter what, because 364 = 26 * 14 (ie, 26
bi-weekly paydays).

If you're original date is in, say, a1, why not just use:
=a1+364

Regards,
Fred

"Greg L" wrote in message
...
OK, I think I've got it and after I thought about it, I don't think
the leap year matters. It's 364 days no matter what.

=MOD(DATE(2009,2,26)-TODAY(),364)+TODAY()

Anyone think this will not work? It seems to so far.

Thanks for all the help :)

On Sun, 28 Jun 2009 18:35:35 -0500, Greg L
wrote:

Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
...
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day( A2))-1)

Otherwise, if you want to update the cell based on the actual cell,
you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date
in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it
to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple,
but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

A1 will still be this year's date next year and it won't update
correctly if I just use =a1+364. That's why I used the MOD syntax.

On Sun, 28 Jun 2009 21:45:47 -0600, "Fred Smith"
wrote:

You're right, it's 364 no matter what, because 364 = 26 * 14 (ie, 26
bi-weekly paydays).

If you're original date is in, say, a1, why not just use:
=a1+364

Regards,
Fred

"Greg L" wrote in message
.. .
OK, I think I've got it and after I thought about it, I don't think
the leap year matters. It's 364 days no matter what.

=MOD(DATE(2009,2,26)-TODAY(),364)+TODAY()

Anyone think this will not work? It seems to so far.

Thanks for all the help :)

On Sun, 28 Jun 2009 18:35:35 -0500, Greg L
wrote:

Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
m...
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day (A2))-1)

Otherwise, if you want to update the cell based on the actual cell,
you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date
in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it
to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple,
but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

please fix your date - we're getting July 2nd which means you stuff my news
group viewer


thanks

"Rajesh Mehmi" wrote in message
...
Greg
Try this - hope it helps
=IF(TODAY()<=D8,D8,IF(MOD(YEAR(D8),4)=0,D8+365,D8+ 364))

Rajesh Mehmi



"Greg L" wrote in message
...
Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
...
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day( A2))-1)

Otherwise, if you want to update the cell based on the actual cell,
you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date
in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it
to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple,
but
I must be missing something.

TIA
Greg L

Update a date into the same cell?
 

Greg
Try this - hope it helps
=IF(TODAY()<=D8,D8,IF(MOD(YEAR(D8),4)=0,D8+365,D8+ 364))

Rajesh Mehmi



"Greg L" wrote in message
...
Now that you mention it, the leap years do make a difference.

I basically want something that will change the date in the cell to
364 days if the dates cross Feb during a non-leap year and 365 if the
dates cross Feb in a leap year.

I'm trying to come up with a date that is 26 (1 year) paydays (every
two weeks) after the original one.

For example: next payday is actually 7/2/2009. I would like if the
date is greater than 7/2/2009 for the cell to update to the new payday
next year of 7/1/2010 (both on Thursday) and continue to do that every
year.

If we could come up with something that would make the function
perpetual (not have to change the control date every year) that would
be great also.

Thanks for your help!

On Sun, 28 Jun 2009 17:05:53 -0600, "Fred Smith"
wrote:

If you always want 364 days after your control date, use:
=if(today()a2,a2+364)

Will general June 30 in non-leap years, and June 29 in leap years.
Is this what you want?

Regards,
Fred.

"Greg L" wrote in message
. ..
That was a good start, but I wanted something that I didn't have to
change every year and would work with leap years, etc.

Any other ideas?

TIA

On Sun, 28 Jun 2009 15:13:01 -0700, Sean Timmons
wrote:

If you have a control cell that shows "7/1/2009", then you can do

=IF(today()A2,date(year(A2)+1,month(A2),day(A 2))-1)

Otherwise, if you want to update the cell based on the actual cell,
you're
looking at Macro, which would be better asked within the Programming
section
of this group.

"Greg L" wrote:

Is there a way to update a date cell with a new date after the date in
the cell has passed?

ie. I have 7/1/2009 in a cell. After 7/1/2009 has passed, I want it to
display the date that is 364 days past 7/1/2009. This would be
6/30/2010.

I've been trying to figure this out for a while now. Seems simple, but
I must be missing something.

TIA
Greg L