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
Dot GlindemannAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)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))

Open in new window

Remember to confirm it with Ctrl + Shift + Enter.
1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.