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

Dot Glindemann used Ask the Experts™
on
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
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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.

Do more with