Something other than DATEVALUE

I have the following formula that uses datevalue to determing what range to lookup. I'd rather not use datevalue as each month I will have to change the value to the first of that month. Is there something I can use that will automatically find the first day of the month based on the month in U4 - which is a drop down menu (Data Validation list containing the dates of the month).

=IF(U4-6<DATEVALUE("08/01/2006"),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000))
Thanks.

-ep

With the year in (say) U5:

Date(U5,U4,1)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"edwardpestian" <[email protected]> wrote in message
news:[email protected]...
|
| I have the following formula that uses datevalue to determing what range
| to lookup. I'd rather not use datevalue as each month I will have to
| change the value to the first of that month. Is there something I can
| use that will automatically find the first day of the month based on
| the month in U4 - which is a drop down menu (Data Validation list
| containing the dates of the month).
|
| =IF(U4-6<DATEVALUE("08/01/2006"),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000))
| Thanks.
|
| -ep
|
|
| --
| edwardpestian
| ------------------------------------------------------------------------
| edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
| View this thread: http://www.excelforum.com/showthread...hreadid=569742
|

Are the dates in the drop down full dates like 8/9/2006?

If so, replace:

DATEVALUE("08/01/2006")

With:

U4-DAY(U4)+1

Biff

"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> I have the following formula that uses datevalue to determing what range
> to lookup. I'd rather not use datevalue as each month I will have to
> change the value to the first of that month. Is there something I can
> use that will automatically find the first day of the month based on
> the month in U4 - which is a drop down menu (Data Validation list
> containing the dates of the month).
>
> =IF(U4-6<DATEVALUE("08/01/2006"),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000))
> Thanks.
>
> -ep
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
> http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=569742
>

So I cannot use the year in U4 too? I tried this, but am getting a #NUM! error.

=IF(U4-6<DATE(U4,U4,1),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000)

Regards,

-ep

Got this to work:

=IF(U4-6<DATE(YEAR(U4),MONTH(U4),DAY(1)),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000

-ep

Try it like this:

DATE(YEAR(U4),MONTH(U4),1)

I like this better:

U4-DAY(U4)+1

Biff

"edwardpestian" <[email protected]>
wrote in message
news:[email protected]...
>
> So I cannot use the year in U4 too? I tried this, but am getting a #NUM!
> error.
>
> =IF(U4-6<DATE(U4,U4,1),HLOOKUP(U4-6,Data!B23:AF27,4,FALSE)/1000,HLOOKUP(U4-6,Data!B5:AF9,4,FALSE)/1000)
>
> Regards,
>
> -ep
>
>
> --
> edwardpestian
> ------------------------------------------------------------------------
> edwardpestian's Profile:
> http://www.excelforum.com/member.php...o&userid=33809
> View this thread: http://www.excelforum.com/showthread...hreadid=569742
>