Formula to VBA codes

S

Savio

is there any way to do this using VBA?

=-(IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)+WEEKDAY(A1,2)>6)+2*(IF
(AND( WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)+WEEKDAY(A1,2)>5)+A1

thanks
 
B

Bernie Deitrick

Savio,

Sure. Just process the formula with Format and Evaluate:

MsgBox Format(Evaluate("-(IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)" & _
"+WEEKDAY(A1,2)>6)+2*(IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)" & _
"+WEEKDAY(A1,2)>5)+A1"), "mmmm dd, yyyy")

HTH,
Bernie
MS Excel MVP
 
D

Dana DeLouis

Hi. Just a note. I may be wrong here though.

+WEEKDAY(A1,2)>5)

This took advantage of Excel returning an implied 1 or 0.
Perhaps do this with the others.
-(IF(AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24),1,0)
Click to expand...
So, subtract 1 if it's a weekday and after hours (>5pm)

AND(WEEKDAY(A1,2)<6,MOD(A1,1)>17/24)

and then later add 2 if it's the same thing..

+2*(IF(AND( WEEKDAY(A1,2)<6,MOD(A1,1)>17/24)

Could these be simplified?

Dana DeLouis
 
Top