Formula from excel to VBA

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I have a date formula that I need to rewrite in VBA. Essentially i
takes a date expressed in the format:m/d/yyyy h:mm, and then adds hour
to it incrementally down rows. The first date is a constant, th
second date is the addition of one hour but the third date onward is
big formula. Effectively the formula keeps adding hours but takes int
account daylight savings in the months of april and october.

I am not sure how to recreate this in VBA due to the brackets used i
excel

In excel it looks like this:

A1 = 1/1/2002 0:00
B2 = a1+1/24
C
=A2+1/24+IF(AND(MONTH(A2)=4,WEEKDAY(A2)=1,HOUR(A2)=1,EOMONTH(A2,0)-A2>22),1/24,0)+IF(AND(MONTH(A2)=10,WEEKDAY(A2)=1,HOUR(A2)=1,HOUR(A1)=0,EOMONTH(A2,0)-A2<7),-1/24,0)

In vba it looks like this:

date1 = a1 = 1/1/2002 0:00
date2 = Serial(date1) + 1/24 'Is this correct?
date3 = ?????????????? 'Recreate big formula

Does anyone know how to do this
 
=A2+1/24+IF(AND(MONTH(A2)=4,WEEKDAY(A2)=1,HOUR(A2)=1,EOMONTH(A2,0)-A2>2
2),1/24,0)+IF(AND(MONTH(A2)=10,WEEKDAY(A2)=1,HOUR(A2)=1,HOUR(A1)=0,EOMO
NTH(A2,0)-A2<7),-1/24,0)
Click to expand...

use IFF instead of IF, and use A AND B instead of AND(A,B).

Also do note that daylight saving time goes on/off at 2am, not at midnight.
 
Back
Top