IF Date is then...
 

I have searched and tried all the weekday/network day etc formulas but
still can't get this to work.

what i have is a column with dates from the begining to end of the
month. and next to it another column with values.

I wnat to make a graph with this, but only want to use the values where
the date falls between Monday and Friday of that week - i dont mind
creating another column for this but i need a fomrula some sort of IF
statement or something to detrermine whether the date is between monday
and friday.


--
vect98
------------------------------------------------------------------------
vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Hi!

There's about a million ways to do this. Here's a couple:

Assume the dates are in the range A1:A31

In your helper column enter:

=A1

Then use a custom format of: dddd. This will display the weekday as
Wednesday but remember now, this is just a number formatted to display as
Wednesday.

OR

=WEEKDAY(A1,2)<6

This will return TRUE for Monday thru Friday and FALSE for Saturday, Sunday

OR

=IF(WEEKDAY(A1,2)<6,"Yes","No")

OR

=CHOOSE(WEEKDAY(A1,2),"Mon","Tue","Wed","Thur","Fr i","Sat","Sun")

Biff

"vect98" wrote in
message ...

I have searched and tried all the weekday/network day etc formulas but
still can't get this to work.

what i have is a column with dates from the begining to end of the
month. and next to it another column with values.

I wnat to make a graph with this, but only want to use the values where
the date falls between Monday and Friday of that week - i dont mind
creating another column for this but i need a fomrula some sort of IF
statement or something to detrermine whether the date is between monday
and friday.


--
vect98
------------------------------------------------------------------------
vect98's Profile:
http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Ive been able to determine the days thanks, but how do i graph it
because the dates will change with each new month so the days will be
in different places and i dont' want to have to redo the graph every
time because the places of the weekdays have changed.

thanks


--
vect98
------------------------------------------------------------------------
vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Ive found if i filter its okay, because the day is in DDDD format i cant
filter with that column, so i thouguht if i make another column and use
an IF formula to determine what day it is then i can filter by saying
not equal to sat/sun except i cant compare with it because of the
format it is in - any ideas???

TIA


--
vect98
------------------------------------------------------------------------
vect98's Profile: http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Charts aren't exactly my strong point.

This is probably the best Excel chart resource on the net:

http://peltiertech.com/Excel/CustomSolutions.html

Biff

"vect98" wrote in
message ...

Ive been able to determine the days thanks, but how do i graph it
because the dates will change with each new month so the days will be
in different places and i dont' want to have to redo the graph every
time because the places of the weekdays have changed.

thanks


--
vect98
------------------------------------------------------------------------
vect98's Profile:
http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Your mistake is, you are trying to apply the wanted result to existing
design. Always start from what do you want to get, and designe your table(s)
accordingly.

You aren't specific enough for us to give any applicable solution, but here
are some ideas - maybe you can use them.

You can create an additional sheet, where a new table is generated
automatically (using various formulas) from original sheet - in layout you
need for your graph.

You can use named ranges in your graph as series references (too little info
at moment to decide, is it possible in your case, and when yes, then how to
do it)

You redesign your current sheet, so it looks like this:
Into cell F1 you enter the 1st of month as a date (like 01.09.2005), and
format it as "yyyy.mmmm"
(you can apply data validation list for this cell, or have there the formula
which returns 1st of current month, or you simply enter the date manually)

Into range A1:C1 enter column headings: Weekday, Day, Value
Into range A2:A43 enter weekdays (Monday through Sunday in my example, when
you want to start the week with Sunday, you have to modify formulas in
column Day).
Into cell B2 enter the formula
=IF(MONTH($F$1-WEEKDAY($F$1,2)+ROW()-ROW($B$1))=MONTH($F$1),$F$1-WEEKDAY($F$1,2)+ROW()-ROW($B$1),"")
and copy down to range B2:B43. Format the range as "dd"

Now, for every month, weekdays remain always on same position (but the
position of dates is changing).

To consolidate this table, so all weekends are excluded, you have to use
another sheet anyway, but formulas there will be much simpler now (you can
use simple links).


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"vect98" wrote in
message ...

Ive been able to determine the days thanks, but how do i graph it
because the dates will change with each new month so the days will be
in different places and i dont' want to have to redo the graph every
time because the places of the weekdays have changed.

thanks


--
vect98
------------------------------------------------------------------------
vect98's Profile:
http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759

Use this in the helper column:

=Weekday(A1,2)<6

Then filter on that column = TRUE.

Biff

"vect98" wrote in
message ...

Ive found if i filter its okay, because the day is in DDDD format i cant
filter with that column, so i thouguht if i make another column and use
an IF formula to determine what day it is then i can filter by saying
not equal to sat/sun except i cant compare with it because of the
format it is in - any ideas???

TIA


--
vect98
------------------------------------------------------------------------
vect98's Profile:
http://www.excelforum.com/member.php...o&userid=26365
View this thread: http://www.excelforum.com/showthread...hreadid=469759