Picking out dates

[Guest]

I have a series of data that comes in calendar day format, I need to be able to pull out only the working days part, 5 days every week. The data is in the form of two columns, first column contains the date that the data refers to and the second column contains the actual data.
For instance:
12/29/1989 50.0
12/30/1989 50.2
12/31/1989 50.1
1/1/1990 50.4

12/30 and 12/31 are both weekend days so I need to find a formula that checks to see if the date is a weekend day and if so skip to the next non-weekend day. I tried using the weekday function which allows me to do the checking part but I'm stuck on how to put it into a decent formula that I can just copy and paste down many rows.

Thanks,
Matt

 

[Kostis Vezerides]

Hi Matt
Assuming dates are in A2:A10, and actual values in B2:B10,
the following ARRAY formula will do:

=SUM(IF(WEEKDAY(A2:A10,2)>5, B2:B10, 0))

Formula must be entered with Shift+Ctrl+Enter

HTH
Kostis Vezerides

-----Original Message-----
I have a series of data that comes in calendar day

format, I need to be able to pull out only the working
days part, 5 days every week. The data is in the form of
two columns, first column contains the date that the data
refers to and the second column contains the actual data.

For instance:
12/29/1989 50.0
12/30/1989 50.2
12/31/1989 50.1
1/1/1990 50.4

12/30 and 12/31 are both weekend days so I need to find a

formula that checks to see if the date is a weekend day
and if so skip to the next non-weekend day. I tried using
the weekday function which allows me to do the checking
part but I'm stuck on how to put it into a decent formula
that I can just copy and paste down many rows.

 

[Frank Kabel]

Hi
try something like
=IF(WEEKDAY(A1,2)>=6,"Weekend","Business day")

 

[Frank Kabel]

Hi
if you want a column of working ndays try the following:
A1:
put your starting date

A2:
=WORKDAY(A1,1)
and copy down

 

[Frank Kabel]

Hi
I'm not sure what you mean with 'skip'. Do you want to copy the values?
do you want to calculate something only for working days?

 

[Frank Kabel]

Hi
for just a function (e.g. to calculate column B) try
=SUMPRODUCT(--(WEEKDAY(A4:A100,1)<6),B4:B100)

 

[Kostis Vezerides]

Matt,
to generate a column of consecutive cells containing dates
but excluding weekends, start in A1 w/ your first date and
then, in A2
=A1 + 1 + IF(weekday(A1, 2)>6, 2)

Notice that weekday w/ 2nd argument==2 makes an easier
check for weekend days (Sat=6, Sun=7)

HTH
Kostis Vezerides

-----Original Message-----
Yes to both, I'd prefer to copy the values of just the

working days, and then I do calculations based on these
values. So essentially I could use a function that
allowed me to calculate based on working days if one
exists.

Right now I'm using this formula:
=IF(OR(WEEKDAY(BB_data!$A4)=7,WEEKDAY(BB_data!$A4) =1),"",BB_data!B4)
this allows me to leave a blank in the space where a

weekend day value would be and if nothing else I can then
just filter out the blanks, but I'd love to find a way to
cleanly give me the values without the blanks.