Update a date into the same cell?

  • Thread starter Thread starter Greg L
  • Start date Start date
G

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
 
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.
 
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
 
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.
 
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!
 
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'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
 
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.
 
please fix your date - we're getting July 2nd which means you stuff my news
group viewer


thanks
 
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)
 
Back
Top