Problem with Date

  • Thread starter Thread starter no answer but some thoughts
  • Start date Start date
N

no answer but some thoughts

Hi Dev,
Sanity check: I tried your formulas starting in B16 on
a scratch sheet and got the expected start-of-month
sequence into the next year for several cycles, so there
doesn't seem to be a problem with the formula.
Could there be some left over bits in your target cells
that are interfering with the formulas? Have you tried
with another start date? Have you tried in another range?

Far from an expert, but an interesting problem.

-----Original Message-----
I have a spreadsheet with

Range BA20: =DATE(1989,1,1)
Range BA21: =DATE(YEAR(BA20),MONTH(BA20)+1,DAY(BA20))

BA21's formula is copied down to BA223.

The problem I'm having is that after
Click to expand...
displaying "12/1/1989" in BA31, BA32 displays "#NUM!". If
I overwrite the formula in BA32 with the value "1/1/1990"
w/o quotes, Excel displays "1/0/1900" w/o quotes and
changes the typed value to the formula "=1/1/1990".
 
Hi Dev

never seen the behaviour either but a couple more ideas:
1) try "format painting" (if you haven't already) from BA31 to BA32 (click
on BA31, click on format painter icon - big yellow paintbrush on the
standard toolbar, click on BA32)
2) right mouse click on the sheet tab, choose view code, and see if there's
any stray worksheet_change or worksheet_selectionchange code in there that's
doing something weird to BA32

good luck

Cheers
julieD


Dev Ashish said:
Hi,

I did try on a different range, but on this particular sheet, I get the
Click to expand...
same behavior. If I try it on a different sheet within the same book, the
formula works as expected.
 
Back
Top