Change formula automatically on 7th day. XL2007

[BRUCE FOOTE]

EXCEL 2007
I have a spreadsheet that graphs the difference of two inserted values daily using the simple formula:
=IF(E945="","",E945-E929) & =IF(F945="","",F945-F929) in column I & J respectively
For example note: Row 945

Column:A-----B--------C----D--E--F--G--I--J
3/27/2011 1:59 AM XXXXXXX -0 46 88 0 0 4.00

There are 10 rows collected daily (row 945 -954).
Every 7th day (Sunday night) the formulas must change to =IF(E945="","",E945) & =IF(F945="","",F945) for one day and then for M- St it must return to (E945="","",E945-E929).

Can someone help me out with a formula or macro which will automatically change the formula on the 7th day? I’m stuck. Thanks.

 

[mikerickson]

Try
=IF(E945="",E9445-(WEEKDAY(TODAY(), 1)=1)*E929)

 

[BRUCE FOOTE]

'=IF(E945="",E9445-(WEEKDAY(TODAY(), 1)=1)*E929) ' looks very good.

I'll try it and get back to you.

Thanks

 

[BRUCE FOOTE]

mikerickson,

sorry for the delay at getting back to you.

I changed the formula to:

=IF(E205="","",IF((WEEKDAY(A205)=1),E205,E205-E187))

and applied it to every cell in columns I & J. This works fine for my application.

Thank you for breaking my 'mental roadblock' & helping me with this simple fix.

Bruce

As an explanation to newer users that might view this post This nested if/else construct utilizes the WEEKDAY fx which evaluates a date (col A) and = 1 when date is Sunday, 2 for Monday, etc. (see 'help' for further explanation). So, if E205 is blank EXCEL prints blank; else if WEEKDAY in col A is a Sunday (1) i.e. if it is true, then it prints the value in cell E205, else if the date = any other day of the week 2-7 (i.e. if false) it prints the difference between E205 & E187.
An explanation of how a formula works has always helped me.