how to check for days of the week

[Gary]

I have several sheets that I need to collect data from. Coloumb A has dates
Sunday thru Saturday, formated as: Tuesday, August 25, 2009 There
are twelve sheets like this....one for each month. On the "Report" sheet, I
need to collect certain 'days of the week' which are Tuesday and Thursdays
only (this may change later) and use the values associated with those days.
The cells that each day is in will change from month to month and year to
year. Is it possible to tell Excell to 'locate' a certain day in a
particular coloumb and bring the associated values in coloumbs B thru H?

I don't always find my way to this area, would you mind if you contact me
at: (e-mail address removed)?

 

[Rajesh Mehmi]

Hi
You can add a column next to date column and put the formula below, copy it
down , This will give you the weekday ( SUNDAY =1)
you can use sum if to get totals for the desired days

=IF(WEEKDAY(D5,1)=1,"Sunday",IF(WEEKDAY(D5,1)=2,"Monday",IF(WEEKDAY(D5,1)=3,"Tuesday",IF(WEEKDAY(D5,1)=4,"Wednesday",IF(WEEKDAY(D5,1)=5,"Thursday",IF(WEEKDAY(D5,1)=6,"Friday",IF(WEEKDAY(D5,1)=7,"Saturday","")))))))

Best regards

Rajesh Mehmi

(e-mail address removed)

 

[Rick Rothstein]

Your entire formula can be replaced by this much shorter one...

=TEXT(D5,"dddd")

 

[Rick Rothstein]

I'm not completely sure what you are trying to do from your description. Can
you explain what you mean by "I need to collect certain days" and "bring the
associated value in columns B thru H"? Collect what, the values in B thru H?
Bring them where (I know the report sheet, but where on the report sheet)?
Also, how are the sheets you are collecting the information from named? Is a
VB coded solution acceptable?

 

[Bernd P]

Hello Gary,

You seem to have collected your data separated by weekday already.
Aren't your 12 sheets showing Sunday data in column B, ... and
Saturday data in column H?

If that's the case you can easily apply a 3D sum:
All Tuesdays: =SUM(Sheet1:Sheet12!D1999)
All Thursdays: =SUM(Sheet1:Sheet12!F1:F999)

(You did not mention your sheet names. Make sure that Sheet1 is your
first, Sheet12 your last and that all the others BUT NO MORE sheets
are in between.)

If I did not guess your data structure correctly let us exactly know
how your data is organised, please.

OT: Don't ask for emails please. Others would like to participate from
all responses.

Regards,
Bernd

 

[Rajesh Mehmi]

Hi Rick

Could the result of =TEXT(D5,"dddd")
then be used in a "sumif" function?


best regards
Rajesh Mehmi
(e-mail address removed)

 

[Rick Rothstein]

I don't see why not (you could always try it and see). Let's put it this
way... if the result of the formula you posted can, then so can mine.... the
two formulas produce identical results.

 

[Rajesh Mehmi]

Hi Rick

I have tried the formula and it does work.
Thanks