Update a date into the same cell?

[Greg L]

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

 

[Sean Timmons]

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.

 

[PtOOey]

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

Thanks a lot

 

[Greg L]

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

 

[Fred Smith]

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]

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!

 

[Greg L]

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

 

[smartin]

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!

 

[Fred Smith]

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]

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.

 

[Patrick Molloy]

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


thanks

 

[Rajesh Mehmi]

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

Rajesh Mehmi
(e-mail address removed)