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

Sure. What does it do and why ?

Best wishes Harald

"Savio" wrote in message
...
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

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(A 1,1)17/24),1,0)" & _
"+WEEKDAY(A1,2)5)+A1"), "mmmm dd, yyyy")

HTH,
Bernie
MS Excel MVP


"Savio" wrote in message
...
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

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)
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


Savio wrote:
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