Date Function

[Gos-C]

Hi,

I have =DATE(2006,1,1) in cell B5 (with custom format mmm yyyy) and entered =DATE(YEAR(B5),MONTH(B5),1) in cell A7 (with custom format d). Then I entered =IF(DATE(YEAR(B5),MONTH(B5),DAY(A7)+1)>DATE(YEAR(B5),MONTH(B5)+1,0),"",DATE(YEAR(B5),MONTH(B5),DAY(A7)+1)) in cell A8 (with custom format d),which I want to copy down the next twenty-nine cells (also with custom format d), but it is giving the error #NUM! What have I done wrong?

Thanks,
Gos-C

[Kevin Vaughn]

You don't mention what is in B6, but since you are using relative reference
for B5, if B6 were a negative number, you could get a #num error when you
copy down. You probably want to make B6 absolute ($B$6).
--
Kevin Vaughn


"Gos-C" wrote:

>
> Hi,
>
> I have *=DATE(2006,1,1)* in cell B5 (with custom format mmm yyyy) and
> entered *=DATE(YEAR(B5),MONTH(B5),1)* in cell A7 (with custom format
> d). Then I entered
> *=IF(DATE(YEAR(B5),MONTH(B5),DAY(A7)+1)>DATE(YEAR(B5),MONTH(B5)+1,0),"",DATE(YEAR(B5),MONTH(B5),DAY(A7)+1))*
> in cell A8 (with custom format d),which I want to copy down the next
> twenty-nine cells (also with custom format d), but it is giving the
> error #NUM! What have I done wrong?
>
> Thanks,
> Gos-C
>
>
> --
> Gos-C
>
>
> ------------------------------------------------------------------------
> Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
> View this thread: http://www.excelforum.com/showthread...hreadid=507893
>
>

[Gos-C]

Thanks, Kevin. The problem was not related to relative cell reference (even though I had to make cell B5 absolute when copying the formula down). Cell B6 had Page 1. I used the same formula on a new sheet and everything worked.

Gos-C

[Kevin Vaughn]

Glad you got it working.
--
Kevin Vaughn


"Gos-C" wrote:

>
> Thanks, Kevin. The problem was not related to relative cell reference
> (even though I had to make cell B5 absolute when copying the formula
> down). Cell B6 had Page 1. I used the same formula on a new sheet and
> everything worked.
>
> Gos-C
>
>
> --
> Gos-C
>
>
> ------------------------------------------------------------------------
> Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518
> View this thread: http://www.excelforum.com/showthread...hreadid=507893
>
>