Occurances from Monday to Friday Using Actual Dates

Stephen_IV · Jul 20, 2011

I would like to thank Alphafrog and Barry Houdini yesterday for their help! What I am trying to do today is to get a count of Monday and Friday based on absences and real dates not ranges. If they occured on Monday and Friday then I would like to have a count. thanks in advance Stephen!

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

AL

AM

AN

AO

AP

AQ

AR

AS

AT

1

Teacher ID

First Name

Last Name

8/25

8/26

8/27

8/30

8/31

9/1

9/2

9/3

9/6

9/7

9/8

9/9

9/10

9/13

9/14

9/15

9/16

9/17

9/20

9/21

9/22

9/23

9/24

9/27

9/28

9/29

9/30

10/1

10/4

10/5

10/6

10/7

10/8

10/11

10/12

10/13

10/14

10/15

10/18

10/19

10/20

10/21

Occurances of Mon-Fri

2

3

XXXX

A

0

3

4

XXXX

A

1

4

5

XXXX

A

0

5

6

XXXX

A

0

6

7

XXXX

A

2

7

8

XXXX

A

0

8

11

XXXX

A

0

9

12

XXXX

A

4

10

13

XXXX

A

0

Sheet1

</body></html>

T. Valko · Jul 20, 2011

Stephen_IV said:
I would like to thank Alphafrog and Barry Houdini yesterday for their help! What I am trying to do today is to get a count of Monday and Friday based on absences and real dates not ranges. If they occured on Monday and Friday then I would like to have a count. thanks in advance Stephen!

You want to count Monday's and Friday's marked with "A" ?

=SUMPRODUCT((WEEKDAY(D1:AS1,2)={1;5})*(D2:AS2="A"))

Stephen_IV · Jul 20, 2011

Thanks for the response Biff,

I would like to count absences as 1 if there is an absence on both Friday and the preceeding Monday. the results that I am looking for are in Column AT. Thanks. This was the link that I was helped on yesterday.
http://www.mrexcel.com/forum/showthread.php?t=565356

T. Valko · Jul 20, 2011

Stephen_IV said:
Thanks for the response Biff,

I would like to count absences as 1 if there is an absence on both Friday and the preceeding Monday. the results that I am looking for are in Column AT. Thanks. This was the link that I was helped on yesterday.
http://www.mrexcel.com/forum/showthread.php?t=565356

You don't have any Monday's in your sample.

The weekdays run from Tuesday thru Saturday.

Stephen_IV · Jul 20, 2011

Sorry Biff the dates are from 2010, the ones in the yellow are Monday and Friday.

MikeWx · Jul 20, 2011

I made a slight modification to Biff's formula, and I think it will give you what you are looking for. Enter it in AT2 and copy it down.

Code:

=SUMPRODUCT((WEEKDAY(D$1:AS$1,2)=1)*(D2:AS2="A")*(OFFSET(D2:AS2,0,-1)="A"))

T. Valko · Jul 20, 2011

MikeWx said:
I made a slight modification to Biff's formula, and I think it will give you what you are looking for. Enter it in AT2 and copy it down.

Code:

=SUMPRODUCT((WEEKDAY(D$1:AS$1,2)=1)*(D2:AS2="A")*(OFFSET(D2:AS2,0,-1)="A"))

I was having a hard time trying to figure this out based on this:

on both Friday and the preceeding Monday.

I interpreted that to mean:

Book1

A

B

C

D

E

F

1

A

_

A

_

2

Mon

Tue

Wed

Thu

Fri

Mon

3

this

_

this

_

Sheet3

But, if you look at the expected results it can't be the preceding Monday. It has to be the Friday and the FOLLOWING Monday.

Stephen_IV · Jul 21, 2011

Biff you are right it is Friday and the FOLLOWING Monday. MikeWx the formula worked perfect.

Biff what I am trying to do is see if there is a pattern of taking Friday and Monday off. we already check to see how many Mondays or Fridays they take off but this gives us another piece of data to analyse.

What I would really like to do is have a date range of Holidays and if there is a holiday on Monday or Friday then use the date before either Thursday or Tuesday? Any help would be much appreciated!

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

1

Teacher ID

First Name

Last Name

8/25

8/26

8/27

8/30

8/31

9/1

9/2

9/3

9/7

9/8

9/9

9/10

9/13

9/14

9/15

9/16

9/17

9/20

9/21

9/22

9/23

9/27

Count

Holidays

2

1

XXXX

A

1

9/6/2010

3

2

XXXX

A

0

9/24/2010

4

3

XXXX

A

0

5

4

XXXX

A

1

6

5

XXXX

0

7

6

XXXX

A

0

8

7

XXXX

A

0

9

8

XXXX

A

0

10

11

XXXX

A

0

11

12

XXXX

A

4

12

13

XXXX

A

0

Sheet1

</body></html>

MikeWx · Jul 23, 2011

Stephen,

The current format of your calendar doesn't show weekends or holidays that occur on Monday or Friday. The formula I gave earlier will identify the instances where a holiday occurs on Friday and the absences occurs on the occur on the preceding Thursday and the Monday following the holiday. In order to also count the Monday holidays, replace my preceding formula with this one:

Code:

=SUMPRODUCT((WEEKDAY(D$1:Y$1,2)=1)*(D2:Y2="A")*(OFFSET(D2:Y2,0,-1)="A"))+SUMPRODUCT((WEEKDAY(E$1:Y$1,2)=2)*((E$1:Y$1)-OFFSET(E$1:Y$1,0,-1)>1)*(E2:Y2="A")*(OFFSET(E2:Y2,0,-1)="A"))

The new portion determines that a Monday holiday occurred by subtracting the date of the day before each Tueday from the date of Tuesday and identifying the Monday as a holiday if the difference is more than one, since a Monday holiday would not be on the calendar.

Stephen_IV · Jul 23, 2011

MikeWx,

You are the man! Thank you so much it did the job! I appreciate it! Thanks again!

Occurances from Monday to Friday Using Actual Dates

Stephen_IV

Well-known Member

Excel Facts

T. Valko

Well-known Member

Stephen_IV

Well-known Member

T. Valko

Well-known Member

Stephen_IV

Well-known Member

MikeWx

Active Member

T. Valko

Well-known Member

Stephen_IV

Well-known Member

MikeWx

Active Member

Stephen_IV

Well-known Member

Similar threads

Forum statistics

Share this page

Occurances from Monday to Friday Using Actual Dates

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock