Cell B1 =IF(OR(A1=DATE(2004,9,1),A1=DATE(2004,11,25)),"Holiday","")
The following VBA code is not working.
Will you help me to correct it? Please.
First,
holiday1 = 9 / 1 / 2004
won't give you the date value for 1 September 2004. It'll give you the
number 9 divided by 1 divided by 2004.
Dates are in fact a serial number starting from 1 for 1 January 1900.
Times are represented by the decimal part of the value. Eg 1.5 is 12
noon on 1 January 1900. If you put a value less than 1 in (as your
formula would have done, had it worked), the cell value would evaluate
to the 0th (sic) of January 1900. (For practical purposes, Excel
doesn't handle dates before 1/1/1900.) 1 September 2004 is in fact day
#38,231. (Enter the date into a cell, then convert the cell format to
General to see that.)
Accordingly you need to either insert the formula so that it refers to
the appropriate serial date (eg,
=IF(OR(A1=38231,A1=38316),"Holiday","")) which isn't going to be all
that intuitive when you look at it, or you insert it using the date
function, just as you've done in the cell itself.
One way:
Sub test()
'Use a constant to add double quotes to formulas,
'otherwise they can be a pain to work with.
'Note that the syntax below yields a one
'double quote character, not two.
Const QuoteDbl = """"
Dim holiday1 As Date
Dim holiday2 As Date
'The following expressions don't evaluate as the
'dates that you want. The first one is the value 9 divided by
'1 divided by 2004.
'holiday1 = 9 / 1 / 2004
'holiday2 = 11 / 25 / 2004
'This syntax would give you the right date values, but you
'don't need it.
holiday1 = DateSerial(2004, 9, 1)
holiday2 = DateSerial(2004, 11, 25)
'There's no real need to use the Cells(1, 1).Address expression;
'you could just hard code A1 or $A$1 if you wanted to.
'It's just there for illustration.
'You could, of course, store the year, month and day as variables
'and add them into the formula if you need more flexibility.
Cells(1, 2).Formula = _
"=IF(OR(" & Cells(1, 1).Address & " = Date(2004,9,1) " _
& "," _
& Cells(1, 1).Address & "= Date(2004,11,25)), " _
& QuoteDbl & "Holiday" & QuoteDbl & "," _
& QuoteDbl & QuoteDbl & ")"
End Sub
