HLOOKUP with current date problems

Kentona

New Member
Joined
Mar 14, 2007
Messages
8
I currently have a spreadsheet which has a number of different accounts on each tab and the front tab I have set up as an overview, which has the current totals for every account for each month of this year. I have the headings set up as mmm-yy.

What I am trying to achieve is to have a column at the end of this overview and just the figures for the current month so that they stand out more and have a HLOOKUP on the figures to the left of it. At the top of this column I have got the current date by using Ctrl+; and also have it formatted to mmm-yy, but the HLOOKUP doesn't recognize it. It does work if I override it with 01/07/07 (for Jul-07). I'm guessing this is because the dates at the top of each the column I used 01/01/07, 01/02/07 etc.

I am using the Windows XP version - does any one have any ideas and I can get this to work?

Thank you in advance.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Rather than using the current date, use this formula to give the 1st of the current month

=TODAY()-DAY(TODAY())+1

This will give 1st July 2007 until the end of this month and then change, automatically, to 1st August 2007 etc. You can format it however you want
 

Forum statistics

Threads
1,181,053
Messages
5,927,848
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top