Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multiple date calculations
Hello.
I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#2
|
|||
|
|||
Put your formula in your 3rd column top row C2.do EditCopy on the cell with
your formula. Select (highlight) from C2: wherever. While range is highlighted, go to Editpaste. The references will all be changed relative to the row address. Double check this works before you start producing data/reports based on this data.. Pat "Bluenose" wrote: Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
#3
|
|||
|
|||
If you go to the bottom right of the cell until the cursor changes to a
thick black cross, you can then click and drag the formula down. It will adjust for each row. BTW, you can use a simpler formula =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months, " & DATEDIF(A1,B1,"md") & " days" but you need the Analysis Toolpak installed. -- HTH RP (remove nothere from the email address if mailing direct) "Bluenose" wrote in message ... Hello. I have a function that allows me to calculate the length of time between two dates. =YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1), DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH(B1)-MONTH(A1)+IF(AND(MONTH(B1) <=MONTH(A1),DAY(B1)<DAY(A1)),11,IF(AND(MONTH(B1)<M ONTH(A1),DAY(B1) =DAY(A1)),12,IF(AND(MONTH(B1)MONTH(A1),DAY(B1)<D AY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days" I have a list of dates in adjacent columns. I want to be able to display the result of this function in a third column, for the two dates in each row. Can anyone tell me whether or not it is possible to somehow do this more easily than pasting the function into each cell of the 3rd column and changing the cell references in the funtion manually? This is not practical as the columns are too large! I only have a limited knowledge of Excel so I apologise if this is a simple query. Hope you clever people can help! Many thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Calculations | Excel Worksheet Functions | |||
date and bond calculations | Excel Discussion (Misc queries) | |||
date and bond calculations | Excel Worksheet Functions | |||
Latest date from multiple list | Excel Worksheet Functions | |||
Best technique to perform multiple calculations ? | Excel Worksheet Functions |