If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
projecting dates
Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine
dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you |
#2
|
|||
|
|||
projecting dates
Try using the below formula in B1
=DATE(YEAR(A1)+70,MONTH(A1),DAY(A1)-1) If this post helps click Yes --------------- Jacob Skaria "Sapper" wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you |
#3
|
|||
|
|||
projecting dates
Refer the below link
http://www.excelfunctions.net/ExcelFunctions.html If this post helps click Yes --------------- Jacob Skaria "Sapper" wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you |
#4
|
|||
|
|||
projecting dates
Sapper wrote:
Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
projecting dates
Why not =A1+69 format cell B1 as date
edvwvw Sapper wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
projecting dates
Because that would add 69 days to the date. The OP wanted to add 70 *years*
to the date, then subtract one day. Regards, Fred. "edvwvw via OfficeKB.com" u42512@uwe wrote in message news:9547bc4230519@uwe... Why not =A1+69 format cell B1 as date edvwvw Sapper wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you -- Message posted via http://www.officekb.com |
#7
|
|||
|
|||
projecting dates
Hi Jacob
once I'd looked up the site that you'd recommended, the formula looked straight forward and logical. Unfortunately, it only reduced the original date by one day. When I dragged the formula to B2, I got a strange date which I have been unable justify Sapper "Jacob Skaria" wrote: Try using the below formula in B1 =DATE(YEAR(A1)+70,MONTH(A1),DAY(A1)-1) If this post helps click Yes --------------- Jacob Skaria "Sapper" wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you |
#8
|
|||
|
|||
projecting dates
The formula has to do what you asked for. Verify that you copied it
correctly. If you still can't find the error, show us the data. What's in A, and what result did you get. Regards, Fred. "Sapper" wrote in message ... Hi Jacob once I'd looked up the site that you'd recommended, the formula looked straight forward and logical. Unfortunately, it only reduced the original date by one day. When I dragged the formula to B2, I got a strange date which I have been unable justify Sapper "Jacob Skaria" wrote: Try using the below formula in B1 =DATE(YEAR(A1)+70,MONTH(A1),DAY(A1)-1) If this post helps click Yes --------------- Jacob Skaria "Sapper" wrote: Hi, I use EXCEL 2003. I have a list a dates in col A. I want to determine dates in the future equal to1 day less than the seventyth anniversary of those dates. I have seen a formula which is entered on three lines to return an answer on a fourth. Problem is the formula is specific to one date. Is there any formula that I can put in B1, that can be dragged down col B so that it will calculate on the corresponding row in col b? Also I have asked many questions in the past few days, all of which have been answered helpfully. Is there a book out there which covers functions and syntax in depth? Thank you |
#9
|
|||
|
|||
projecting dates
Hi Jacob, I have been trying to contact you for 24hours but the reply service
has been unavailable. Although the formula as offered didn't work, I could see the logic. I Played around with the formula and got the result that I wanted. I could never have got there on my own, so you did help me big style so big thank you =DATE((YEAR(D9)+70),MONTH(D9),DAY(D9)-1) sapper |
#10
|
|||
|
|||
projecting dates
Hi Fred
Ithink there was a bracket missing from original post formula below worked Thanks for input Sapper =DATE((YEAR(D9)+70),MONTH(D9),DAY(D9)-1) |
|
Thread Tools | |
Display Modes | |
|
|