CODEWITHSUNDEEP
excelexcel-formulalookup
Swiftslide
Swiftslide

Reputation: 1347

EXCEL: Look up today's date in the first row of a sheet

I have a sheet with a sequence of dates as column headers. The dates are entered manually and are not necessarily contiguous.

I want a formula that can look up today's date in this sheet and return a value from that column. I have used HLOOKUP with the date entered as a string, i.e.

HLOOKUP("16/08/2012", Statuses!$1:$5, 5, FALSE)

However, I don't want to have to enter the date manually, I want to use the TODAY() formula. But replacing the string with this formula returns #N/A. I'm guessing because the date has been entered manually on the lookup sheet, the value isn't exactly the same.

Is there any way to remedy this, like limiting the return of the TODAY() formula to the very beginning of the day?

Upvotes: 2

Views: 7736

Answers (1)

Adrian
Adrian

Reputation: 2875

Try HLOOKUP(TEXT(TODAY(),"dd/mm/yyyy"),Statuses!$1:$5, 5, FALSE)

That should convert TODAY() to a set date format that matches your above format.

Edit: Of course, this is dependent upon your date column headers being text, which I assume they are since just lookup up TODAY() didn't work.

Upvotes: 5

Related Questions