Help..Need formula

[Gor_yee]

I need one that will use excel to figure what is today's date in the
"dd-mmm-yyyy" format as well as same day last week. For example,

if today was Wednesday 06-May-2009, I need it to return the following
data, 06-may-2009,04-may-2009,03-may-2009,29-apr-2009,28-apr-2009,27-
apr-2009. As you can see, it is also getting the same Day as last
week. I need it to also validate that if today is not Thursday 07-
may-2009 yet then it wont return me data for the 30-apr-2009. Do i
make any sense?

thanks heaps

 

[Ron@Buy]

"Do i make any sense?" - - - - - - No
"it is also getting the same Day as last week"
What is your logic? is it only 06-May-2009 that you want to calculate from?
and many other questions.
Try restating your problem using cell references and fully detail what
results you require.

 

[Gor_yee]

OK...so at the moment i have the following formula :

=TEXT(TODAY(),"dd-mmm-yyyy") & "," & TEXT(TODAY()-1,"dd-mmm-yyyy") &
"," & TEXT(TODAY()-2,"dd-mmm-yyyy") & "," & TEXT(TODAY()-3,"dd-mmm-
yyyy") & "," & TEXT(TODAY()-4,"dd-mmm-yyyy") & "," & TEXT(TODAY
()-5,"dd-mmm-yyyy") & "," & TEXT(TODAY()-6,"dd-mmm-yyyy") & "," & TEXT
(TODAY()-7,"dd-mmm-yyyy") & "," & TEXT(TODAY()-8,"dd-mmm-yyyy") & ","
& TEXT(TODAY()-9,"dd-mmm-yyyy") & "," & TEXT(TODAY()-10,"dd-mmm-
yyyy") & "," & TEXT(TODAY()-11,"dd-mmm-yyyy") & "," & TEXT(TODAY
()-12,"dd-mmm-yyyy")

my issue is that this formula will refresh everyday, right?? So in
this example..if today's date would be Wednesday 06th May then
obviously the formula wouldnt work as it would take me back to 24-
apr-2009. What my result should be if today is Wednesday 06th May is
that it should display

06-may-2009,05-may-2009,04-may-2009,29-apr-2009,28-apr-2009,27-
apr-2009

If today's date was 05-may, my result should show :

05-may-2009, 04-may-2009, 28-apr-2009,27-apr-2009

as you can see, the dates is always matching the same "weekday" for
this week compared to last week.

Am i making a bit more sense now??

 

[Ron@Buy]

A solution, although not very elegant (!):
Somewhere on your spread sheet enter the following (if you enter it on a
blank worksheet you can copy and paste it into a suitably discreet location
on your working spreadsheet)
Cell A1 =TODAY(), Cell B1 =WEEKDAY(TODAY(),2)
Number A2 thro' A8, 1,2,3,4,5,6,7
Cell B2 =TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")
Cell B3
=TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-1,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")&","&TEXT(A1-8,"dd-mm-yyyy")
Cell B4
=TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-1,"dd-mm-yyyy")&","&TEXT(A1-2,"dd-mm-yyyy")&","&TEXT(A1-3,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")&","&TEXT(A1-8,"dd-mm-yyyy")&","&TEXT(A1-9,"dd-mm-yyyy")&","&TEXT(A1-10,"dd-mm-yyyy")
Cell B5
=TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-1,"dd-mm-yyyy")&","&TEXT(A1-2,"dd-mm-yyyy")&","&TEXT(A1-3,"dd-mm-yyyy")&","&TEXT(A1-4,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")&","&TEXT(A1-8,"dd-mm-yyyy")&","&TEXT(A1-9,"dd-mm-yyyy")&","&TEXT(A1-10,"dd-mm-yyyy")&","&TEXT(A1-11,"dd-mm-yyyy")
Cell B6
=TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-1,"dd-mm-yyyy")&","&TEXT(A1-2,"dd-mm-yyyy")&","&TEXT(A1-3,"dd-mm-yyyy")&","&TEXT(A1-4,"dd-mm-yyyy")&","&TEXT(A1-5,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")&","&TEXT(A1-8,"dd-mm-yyyy")&","&TEXT(A1-9,"dd-mm-yyyy")&","&TEXT(A1-10,"dd-mm-yyyy")&","&TEXT(A1-11,"dd-mm-yyyy")&","&TEXT(A1-12,"dd-mm-yyyy")
Cell B7
=TEXT(A1,"dd-mm-yyyy")&","&TEXT(A1-1,"dd-mm-yyyy")&","&TEXT(A1-2,"dd-mm-yyyy")&","&TEXT(A1-3,"dd-mm-yyyy")&","&TEXT(A1-4,"dd-mm-yyyy")&","&TEXT(A1-5,"dd-mm-yyyy")&","&TEXT(A1-6,"dd-mm-yyyy")&","&TEXT(A1-7,"dd-mm-yyyy")&","&TEXT(A1-8,"dd-mm-yyyy")&","&TEXT(A1-9,"dd-mm-yyyy")&","&TEXT(A1-10,"dd-mm-yyyy")&","&TEXT(A1-11,"dd-mm-yyyy")&","&TEXT(A1-12,"dd-mm-yyyy")&","&TEXT(A1-13,"dd-mm-yyyy")
Then a cell where you require your result:
=IF(OR(WEEKDAY(A1,2)=6,WEEKDAY(A1,2)=7),0,VLOOKUP(B1,A2:B8,2))
You can omit cells formulas in B6 & B7, as these cover weekends, and adjust
above formula to =VLOOKUP(B1,A2:B8,2)
Hope this is what you require