# What formula do I use to record the last date recorded in a column

I am trying to set up a formula that will record the last date entered into a specific column (Column D from cell D13 to Cell D2000) into another cell on the same sheet.

The date in D13 is generated by a formula that records the date only if a specific term "C Service" is entered into cell C13 and a  date has been recorded in column A.  All dates are entered as dd/mm/yyyyy

(eg) =IF(\$C13="C Service",TEXT(\$A13,"dd/mmm/yyyy"),"")  (This formula works correctly)

I have tried various formulas to try and get the last date that is recorded in cells D13:D2000 but I cannot get them to give me the correct answer.

These are some of the fomulas I have tried.

=INDEX(\$D\$13:\$D\$2000,MATCH(REPT("z",255),\$D\$13:\$D\$2000))

=INDEX(\$D\$13:\$D\$2000,MATCH(REPT("dd/mmm/yyyy",255),\$D\$13:\$D\$2000))

=MAX(\$D13:\$D2000)

=LARGE(D13:D2000,1)

Any help would be appreciated.

Thanks
Dot
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Excel & VBA ExpertCommented:
Try this Array Formula which requires confirmation with Ctrl + Shift + Enter instead of Enter alone.
``````=INDEX(D13:D2000,LARGE(IF(D13:D2000<>"",ROW(D13:D2000)-ROW(D13)+1),1))
``````
Remember to confirm it with Ctrl + Shift + Enter.
1

Experts Exchange Solution brought to you by