Multiple date calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)<MONTH(A1),DAY(B1)
=DAY(A1)),12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(A1)),-1)))&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
Click to expand...

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
 
Put your formula in your 3rd column top row C2.do Edit>Copy on the cell with
your formula. Select (highlight) from C2: wherever. While range is
highlighted, go to Edit>paste. 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
 
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 said:
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)<MONTH(A1),DAY(B1)
Click to expand...
"&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY(A1),1,0),DAY(A1))&" days"
 
Back
Top