Auto sum day and date without weekends

[michael35]

Hi

I have a month and workweek indicated in Row 1. Can I check is there a way to auto calculate the day and date without including weekends when the value is keyed in cell A1.

[FlameRetired]

I inserted a row. With the value keyed into A1 this in A5 and filled across.

Please Login or Register  to view this content.

Is this what you had in mind?

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2/15/2019

2

Feb WW08

Feb WW09

Mar WW10

3

M

T

W

T

F

M

T

W

T

F

M

T

W

T

F

4

18

19

20

21

22

25

26

27

28

1

4

5

6

7

8

5

2/18/2019

2/19/2019

2/20/2019

2/21/2019

2/22/2019

2/25/2019

2/26/2019

2/27/2019

2/28/2019

3/1/2019

3/4/2019

3/5/2019

3/6/2019

3/7/2019

3/8/2019

[michael35]

Hi Dave,

Its something similar but the rows that i required to auto sum is in Row 3 to show the day (M, T, W, T, F) and in row 4 to show the 2 digit numeral of the date based on the month/WW keyed in the second row.

[Bo_Ry]

Please try Key 18/2/2019 in A3, set custom format to dd
Drag across > Autofill option > fill weekday
Untitled.png


or Formula
set custom format to dd
A4
=(RIGHT(A1,2)-1)*7+"1jan"-WEEKNUM("1jan")

B4 drag acoss
=WORKDAY($A4,COLUMNS($B4:B4))

[FlameRetired]

So you mean like this?

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2/15/2019

2

Feb WW08

Feb WW09

Mar WW10

3

M

T

W

T

F

M

T

W

T

F

M

T

W

T

F

4

18

19

20

21

22

25

26

27

28

01

04

05

06

07

08

In A4

Please Login or Register  to view this content.

and custom format "dd".
In A3

Please Login or Register  to view this content.

In A2 and across the merged cells (my posting macro won't show merged cells).

Please Login or Register  to view this content.

[michael35]

Both worked team. thanks

[FlameRetired]

You are welcome. Glad to help. Thank you for the feedback and marking your thread Solved.