A formula to stop another formula from operating in some instances

[adhide]

I am wanting my newly developed time formula developed with the great help of excalforum, to only operate on particular days similar to the following:

A1 = 21/08/2009
A2 = '=A1' cell formatted to dddd only to show the day {Friday}

I need another cell containing a formula to calculate on any day Monday to Friday, but not Saturday or sunday.

I tried =if(A2="Saturday","",if(a2="Sunday","",IF(P6=1,"",((D6>E6)*MEDIAN(0,E6-'Front Sheet'!C18,'Front Sheet'!C19-'Front Sheet'!C18)+MAX(0,MIN('Front Sheet'!C19,E6+(D6>E6))-MAX('Front Sheet'!C18,D6)))*24)))

But i dont think its recognising A2 as the word "Saturday" as its only a formatted version of A1.

Ive tried a text() equation but i dont think thats right. how can i get this to work?

[solnajeff]

Hi

Why not something like

Please Login or Register  to view this content.

Regards

Jeff

[adhide]

If i do this

=IF(WEEKDAY(A1,2)<6,IF(P6=1,"",((D6>E6)*MEDIAN(0,E6-'Front Sheet'!C18,'Front Sheet'!C19-'Front Sheet'!C18)+MAX(0,MIN('Front Sheet'!C19,E6+(D6>E6))-MAX('Front Sheet'!C18,D6)))*24))

I get the field returning 'False'

[DonkeyOte]

Perhaps:

Please Login or Register  to view this content.

[adhide]

This looks great, and it works, but I would like to know how it does, as i need to do a similar thing in another field that only populate the formule if the day is a saturday, and in another cell,if it is a sunday.

[DonkeyOte]

For more info. I would suggest looking up WEEKDAY function in XL Help, it's quite detailed.

Re: IF ...

=IF(test,do if test true, do if test false)
=IF(OR(test1,test2),do if either test1 or test2 are true, do if both are false)
=IF(AND(test1,test),do if both test1 and test2 are true, do if either are false)

Daddylonglegs has already gone through the complex bit - ie the date formula.