MS XL and IF statements

  toga 09:39 15 Mar 2004
Locked

Hi I am trying to set dates in cells (via a macro)in a XL worksheet, which will automatically update once a month on the click of a button e.g.

15-sep-03
16-sep-03
17-sep-03
18-sep-03
19-sep-03
20-sep-03
21-sep-03

I have tried setting the first cell date and then just adding an increment of one to the second e.g. =A1+1 .However, when I try this the formatting is all wrong.

The increment of 1 works when the first cells date is set as e.g. =Date(2004,03,01) but this pre defined function will not accept a new date automatically.

Hope this makes sence any ideas.

Toga

  VoG II 16:54 15 Mar 2004

I'm not clear what you are trying to do but adding 1 will work provided the target cell is formatted as a Date.

If you are writing this in Visual Basic for Applications, you can do this with a single line of code:


Range("B1").Value = Format(Range("A1").Value + 1, "dd-mmm-yy")

  Simsy 17:43 15 Mar 2004

is the "click of a button" supposed to do; Does it just put the current date in the first cell?

If so, have you formatted this date within the macro? Are all the other relevant cells formatted the same?

I presume this button is a VBA button, from the toolbox?

Regards,

Simsy

  Simsy 17:57 15 Mar 2004

something else has occurred to me...

If you have the format set to something other than the standard Excel ones, eg you are including the day of the week in the cell, such as;

Saturday 13 Mar 2004

it seems that Excel has a problem giving this a value, hence it can't add 1, because it doesn't know what to add it to.

That certainly seems to be the case with my Excel 2000

Does that help at all?

Regards,

Simsy

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?