Converting Excel Formula to VBA Script

[euge_prime2001]

Hi Guys,
I would like to ask your help how to convert these excel formula into VBA Script:
Beg_Date: =DATE(YEAR(TODAY())-1,10,1)
End_Date: =TODAY()
Week No: = Beg_Date-End_Date

Thanks a lot for your help!

Regards,
Eugene

 

[euge_prime2001]

Noted Joe. The formula works fine now:

mBegDate = DateSerial(Year(Now()) - 1, 10, 1)
mEndDate = Now()
mWKNum = Application.RoundUp((mEndDate - mBegDate) / 7, 0)
MsgBox mWKNum

Regards,
Eugene

 

[Joe4]

Ah, yes. One point of clarification I should have mentioned earlier. In VBA, Now() includes a time component. Date does not.
So, if you want current date and time, use Now().
If you just want the current date, use Date.

Based on your calculations, I didn't think you really wanted to include the time.
Note that if you are checking to see if two dates are equal, you probably do not want to include the time piece, or it probably won't work!
Now() = Date for only one second each day (at exactly midnight)!