Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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! |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
Update a date into the same cell?
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update date when value in another cell changes | Excel Discussion (Misc queries) | |||
Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
input a date or update it based on date in another cell | New Users to Excel | |||
Populating Last Saved Date in Cell AND also update that same cell in Header | Excel Discussion (Misc queries) | |||
How to update a cell if a specific date is included in a date rang | Setting up and Configuration of Excel |