# Thread: Last Date Data Is Entered (2000)

1. ## Last Date Data Is Entered (2000)

I have a table of data that I enter each weekday, but not Saturdays, Sundays or holidays. I am trying to find a function that will return the last day data has been entered. For example, I have the month of July formatted out in a worksheet, days 1-31 entered in B2:AF2. I have data entered data for the 1st through the 3rd in cells B33 and F3, but not in E3 (i. e., a holiday) or G3:H3, i. e., Saturday and Sunday. Instead, in cells E3 and G3:H3, I have "N/A". I would like a formula, say in AE2, that would tell me the last day data has been entered, but not a "N/A". In the instant case, the formula in cell AE2 would return the value in cell F2 since this is the last day numerical (as opposed to "N/A") data has been entered.
Any thoughts?
Thanks,
Jeff

2. ## Re: Last Date Data Is Entered (2000)

Try the following formula, entered as an array formula (confirm with Ctrl+Shift+Enter):

=MAX(IF(ISNUMBER(B3:AF3),B2:AF2,""))

3. ## Re: Last Date Data Is Entered (2000)

Doesn't seem to work for me. In my table, I have data entered in cells B33, and F3, corresponding to the 1st-3rd, and 5th of July. Your formula seems to return a "4" for me, that would in turn reference column E (I think!), and that in turn would correspond to the 4th (i. e., in E2), where I have a "N/A" entered in cell E3!
Any other thoughts?
Thanks,
Jeff

5. ## Re: Last Date Data Is Entered (2000)

Well, I must not have been holding my mouth the right way! LOL!!!
Anyway, when I tried the formula again for the last date, it worked. However, when I tried to insert it and get the data that corresponds to the last date, it returned instead the first date's data.
I have attached my worksheet.
Any ideas?
Thanks again,
Jeff

6. ## Re: Last Date Data Is Entered (2000)

Originally, you asked for the last day data had been entered. Now you want the data entered on the last day.

7. ## Re: Last Date Data Is Entered (2000)

Hans,
That's right. Originally I asked for the last date. However, in the meantime, I realized I need the last data as well. I erroneously assumed I could plug in your solution for the last date to get the last data as well, but to no avail. Anyway, thanks-it works fine!
Regards,
Jeff

8. ## Re: Last Date Data Is Entered (2000)

Hans,
Just for my education-what does putting the respective formulas in an array-type format do, as opposed to another way? The reason I ask is that I know absolutely zero about arrays!
Thanks,
Jeff

9. ## Re: Last Date Data Is Entered (2000)

Jeff,

Array formulas enable you - among other things - to do a calculation in one formula that would otherwise take a whole lot of intermediate cells.

Have a look at the links provided in the replies to <!post=post 123094,123094>post 123094<!/post>. Chip Pearson and Bob Umlas explain it far better than I ever could. You can also do a search for "array formula" (entire phrase) on this Forum.

Regards,
Hans

10. ## Re: Last Date Data Is Entered (2000)

Thanks again, Hans.

11. ## Re: Last Date Data Is Entered (2000)

Jeff,

Is the N/A you mention just N/A, a text value, or #N/A, the error value, we have in Excel?

12. ## Re: Last Date Data Is Entered (2000)

It is a text entry I make (or will make) manually. Does that make a difference?
Thanks,
Jeff

13. ## Re: Last Date Data Is Entered (2000)

Yes, it makes lots of diiference Jeff. #N/A would make impossible to use the following efficient formula, while N/A as text entry is just OK:

=OFFSET(A4,0,MATCH(9.99999999999999E+307,A4:AF4)-1,1,1)

will give you the last (non-N/A) entry from A4:AF4 (see the attachment ).

Note that this is not an array-formula.