If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Work Day Calendar

Thread starter Vijeshkv
Start date Apr 19, 2023

V

Vijeshkv

New Member

Apr 19, 2023

#1

Hello ,

Is there any formula we can use to get result in Column E (WD Day )

Posting Month	Entry Date	Posting Month_Entry Date	Week Day	WD Day	Workday Group	Period	Quarter Close/Non Qtr	US Public Holiday
1/1/2023	1/1/2023	44927_44927	Sunday	WD-21	Non Month Close	1	Non Quarter End
1/1/2023	1/2/2023	44927_44928	Monday	WD-21	Non Month Close	1	Non Quarter End	New Year’s: Monday, January 2
1/1/2023	1/3/2023	44927_44929	Tuesday	WD-20	Non Month Close	1	Non Quarter End
1/1/2023	1/4/2023	44927_44930	Wednesday	WD-19	Non Month Close	1	Non Quarter End
1/1/2023	1/5/2023	44927_44931	Thursday	WD-18	Non Month Close	1	Non Quarter End
1/1/2023	1/6/2023	44927_44932	Friday	WD-17	Non Month Close	1	Non Quarter End
1/1/2023	1/7/2023	44927_44933	Saturday	WD-16	Non Month Close	1	Non Quarter End
1/1/2023	1/8/2023	44927_44934	Sunday	WD-16	Non Month Close	1	Non Quarter End
1/1/2023	1/9/2023	44927_44935	Monday	WD-16	Non Month Close	1	Non Quarter End
1/1/2023	1/10/2023	44927_44936	Tuesday	WD-15	Non Month Close	1	Non Quarter End
1/1/2023	1/11/2023	44927_44937	Wednesday	WD-14	Non Month Close	1	Non Quarter End
1/1/2023	1/12/2023	44927_44938	Thursday	WD-13	Non Month Close	1	Non Quarter End
1/1/2023	1/13/2023	44927_44939	Friday	WD-12	Non Month Close	1	Non Quarter End
1/1/2023	1/14/2023	44927_44940	Saturday	WD-11	Non Month Close	1	Non Quarter End
1/1/2023	1/15/2023	44927_44941	Sunday	WD-11	Non Month Close	1	Non Quarter End
1/1/2023	1/16/2023	44927_44942	Monday	WD-11	Non Month Close	1	Non Quarter End	Martin Luther King Jr.: Monday, January 16
1/1/2023	1/17/2023	44927_44943	Tuesday	WD-11	Non Month Close	1	Non Quarter End
1/1/2023	1/18/2023	44927_44944	Wednesday	WD-10	Non Month Close	1	Non Quarter End
1/1/2023	1/19/2023	44927_44945	Thursday	WD-9	Non Month Close	1	Non Quarter End
1/1/2023	1/20/2023	44927_44946	Friday	WD-8	Non Month Close	1	Non Quarter End
1/1/2023	1/21/2023	44927_44947	Saturday	WD-7	Non Month Close	1	Non Quarter End
1/1/2023	1/22/2023	44927_44948	Sunday	WD-7	Non Month Close	1	Non Quarter End
1/1/2023	1/23/2023	44927_44949	Monday	WD-7	Non Month Close	1	Non Quarter End
1/1/2023	1/24/2023	44927_44950	Tuesday	WD-6	Non Month Close	1	Non Quarter End
1/1/2023	1/25/2023	44927_44951	Wednesday	WD-5	Month Close	1	Non Quarter End
1/1/2023	1/26/2023	44927_44952	Thursday	WD-4	Month Close	1	Non Quarter End
1/1/2023	1/27/2023	44927_44953	Friday	WD-3	Month Close	1	Non Quarter End
1/1/2023	1/28/2023	44927_44954	Saturday	WD-2	Month Close	1	Non Quarter End
1/1/2023	1/29/2023	44927_44955	Sunday	WD-2	Month Close	1	Non Quarter End
1/1/2023	1/30/2023	44927_44956	Monday	WD-2	Month Close	1	Non Quarter End
1/1/2023	1/31/2023	44927_44957	Tuesday	WD-1	Month Close	1	Non Quarter End
1/1/2023	2/1/2023	44927_44958	Wednesday	WD+1	Month Close	1	Non Quarter End
1/1/2023	2/2/2023	44927_44959	Thursday	WD+2	Month Close	1	Non Quarter End
1/1/2023	2/3/2023	44927_44960	Friday	WD+3	Month Close	1	Non Quarter End
1/1/2023	2/4/2023	44927_44961	Saturday	WD+3	Month Close	1	Non Quarter End
1/1/2023	2/5/2023	44927_44962	Sunday	WD+3	Month Close	1	Non Quarter End
1/1/2023	2/6/2023	44927_44963	Monday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/7/2023	44927_44964	Tuesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/8/2023	44927_44965	Wednesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/9/2023	44927_44966	Thursday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/10/2023	44927_44967	Friday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/11/2023	44927_44968	Saturday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/12/2023	44927_44969	Sunday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/13/2023	44927_44970	Monday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/14/2023	44927_44971	Tuesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/15/2023	44927_44972	Wednesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/16/2023	44927_44973	Thursday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/17/2023	44927_44974	Friday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/18/2023	44927_44975	Saturday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/19/2023	44927_44976	Sunday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/20/2023	44927_44977	Monday	WD>+3	Post Month Close	1	Non Quarter End	President’s Day: Monday, February 20
1/1/2023	2/21/2023	44927_44978	Tuesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/22/2023	44927_44979	Wednesday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/23/2023	44927_44980	Thursday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/24/2023	44927_44981	Friday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/25/2023	44927_44982	Saturday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/26/2023	44927_44983	Sunday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/27/2023	44927_44984	Monday	WD>+3	Post Month Close	1	Non Quarter End
1/1/2023	2/28/2023	44927_44985	Tuesday	WD>+3	Post Month Close	1	Non Quarter End

Excel Facts

Can you AutoAverage in Excel?

Click here to reveal answer

There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Sort by date Sort by votes

awoohaw

Well-known Member

Apr 19, 2023

#2

Can you clear up some information, please?
what does WD+/-# mean? I am sure there is but it would help to know what the concept of the calculation is.

Upvote 0

V

Vijeshkv

New Member

Apr 19, 2023

#3

awoohaw said:
Can you clear up some information, please?
what does WD+/-# mean? I am sure there is but it would help to know what the concept of the calculation is.

WD+ means entries related to next month
WD- entries related to posting month.

Upvote 0

awoohaw

Well-known Member

Apr 19, 2023

#4

Related HOW? Please give a verbal explanation of how the numbers are calculated and what columns in the worksheet are used to calculate the #?

Upvote 0

V

Vijeshkv

New Member

Apr 19, 2023

#5

This we basically uses for Accounting Calendars
Entry date is used for calculating the WD
Posting month and Entry date use

Upvote 0

awoohaw

Well-known Member

Apr 19, 2023

#6

that is of no help. What do you want?

If you just want random numbers and pick a + or - on a whim, here is a random calculator for WD with +/- and a number less than 30:

Excel Formula:

=INDEX("WD" & CHOOSE(RANDARRAY(1,30,1,2,1),"-","+")&RANDARRAY(1,30,1,30,1),RANDARRAY(1,1,1,30,1))

Book1

A

B

C

1

2

3

Sheet3

Cell Formulas
Range	Formula
B2	B2	=INDEX("WD" & CHOOSE(RANDARRAY(1,30,1,2,1),"-","+")&RANDARRAY(1,30,1,30,1),RANDARRAY(1,1,1,30,1))

Last edited: Apr 19, 2023

Upvote 0

V

Vijeshkv

New Member

Apr 19, 2023

#7

Not a random numbers
if you look at the data which i provided there is a Posting Month and Entry date , Posting month is Jan 2023 , and Entry date range is Jan and Feb month full.

for me Jan 31 is always WD-1 . Jan 30 is WD-2 , if Saturday Sunday and holiday comes, we jus consider previous days WD for that date
for Feb entry date Feb 1st Start with WD+1 , WD+2 so on , same rule of holiday and sat Sunday apply here also

Upvote 0

awoohaw

Well-known Member

Apr 19, 2023

#8

Vijeshkv said:
Not a random numbers
if you look at the data which i provided there is a Posting Month and Entry date , Posting month is Jan 2023 , and Entry date range is Jan and Feb month full.

for me Jan 31 is always WD-1 . Jan 30 is WD-2 , if Saturday Sunday and holiday comes, we jus consider previous days WD for that date
for Feb entry date Feb 1st Start with WD+1 , WD+2 so on , same rule of holiday and sat Sunday apply here also

I know it wasn't random numbers you needed. i asked twice for the information you gave in the last paragraph. not everyone in this forum uses does accounting (i'm assuming that is what your worksheet is about) or uses terminology as you have described. Stay tuned, you've provided the information the forum needs!

Upvote 0

awoohaw

Well-known Member

Apr 19, 2023

#9

I wrote too soon. If you don't count Saturday/Sundays/Holidays then the EOM -21 in in December?
Please give a list of the date of the expected days.

mr excel questions 24.xlsm

A

B

C

D

E

F

G

H

1

Posting Month

Entry Date

Week Day

WD Day

Adj Day

calculation

comment

HOLIDAY SCHEDULE

2

2023-01-01

2023-01-01

Sunday

WD-21

21 days before Jan Monthend?

2023-01-02

3

2023-01-01

2023-01-02

Monday

WD-21

2023-01-16

4

2023-01-01

2023-01-03

Tuesday

WD-20

2023-02-20

5

2023-01-01

2023-01-04

Wednesday

WD-19

6

2023-01-01

2023-01-05

Thursday

WD-18

7

2023-01-01

2023-01-06

Friday

WD-17

8

2023-01-01

2023-01-07

Saturday

WD-16

vijeshkv

Cell Formulas
Range	Formula
E2:E8	E2	=SUBSTITUTE(SUBSTITUTE(D2,"WD",""),">","")*1
F2:F8	F2	=WORKDAY.INTL(EOMONTH(A2,0),E2,1,$H$2:$H$4)

Upvote 0

awoohaw

Well-known Member

Apr 19, 2023

#10

This does the date change ignoring intervening days and only checking if the ## day is a Sat, Sun, or Holiday.
I removed some columns not needed for the calculation.

mr excel questions 24.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

1

Posting Month

Entry Date

Posting Month_Entry Date

Week Day

WD Day

calculation

Period

Workday Group

Period

Quarter Close/Non Qtr

US Public Holiday

HOLIDAY SCHEDULE

2

2023-01-01

2023-01-01

WD-21

2023-01-02

3

2023-01-01

2023-01-02

WD-21

2023-01-16

4

2023-01-01

2023-01-03

WD-20

2023-02-20

5

2023-01-01

2023-01-04

WD-19

6

2023-01-01

2023-01-05

WD-18

7

2023-01-01

2023-01-06

WD-17

8

2023-01-01

2023-01-07

WD-16

9

2023-01-01

2023-01-08

WD-16

10

2023-01-01

2023-01-09

WD-16

11

2023-01-01

2023-01-10

WD-15

12

2023-01-01

2023-01-11

WD-14

13

2023-01-01

2023-01-12

WD-13

14

2023-01-01

2023-01-13

WD-12

15

2023-01-01

2023-01-14

WD-11

16

2023-01-01

2023-01-15

WD-11

17

2023-01-01

2023-01-16

WD-11

18

2023-01-01

2023-01-17

WD-11

19

2023-01-01

2023-01-18

WD-10

20

2023-01-01

2023-01-19

WD-9

21

2023-01-01

2023-01-20

WD-8

22

2023-01-01

2023-01-21

WD-7

23

2023-01-01

2023-01-22

WD-7

24

2023-01-01

2023-01-23

WD-7

25

2023-01-01

2023-01-24

WD-6

26

2023-01-01

2023-01-25

WD-5

27

2023-01-01

2023-01-26

WD-4

28

2023-01-01

2023-01-27

WD-3

29

2023-01-01

2023-01-28

WD-2

30

2023-01-01

2023-01-29

WD-2

31

2023-01-01

2023-01-30

WD-2

32

2023-01-01

2023-01-31

WD-1

33

2023-01-01

2023-02-01

WD+1

34

2023-01-01

2023-02-02

WD+2

35

2023-01-01

2023-02-03

WD+3

36

2023-01-01

2023-02-04

WD+3

37

2023-01-01

2023-02-05

WD+3

38

2023-01-01

2023-02-06

WD>+3

39

2023-01-01

2023-02-07

WD>+3

40

2023-01-01

2023-02-08

WD>+3

41

2023-01-01

2023-02-09

WD>+3

42

2023-01-01

2023-02-10

WD>+3

43

2023-01-01

2023-02-11

WD>+3

44

2023-01-01

2023-02-12

WD>+3

45

2023-01-01

2023-02-13

WD>+3

46

2023-01-01

2023-02-14

WD>+3

47

2023-01-01

2023-02-15

WD>+3

48

2023-01-01

2023-02-16

WD>+3

49

2023-01-01

2023-02-17

WD>+3

50

2023-01-01

2023-02-18

WD>+3

51

2023-01-01

2023-02-19

WD>+3

52

2023-01-01

2023-02-20

WD>+3

53

2023-01-01

2023-02-21

WD>+3

54

2023-01-01

2023-02-22

WD>+3

55

2023-01-01

2023-02-23

WD>+3

56

2023-01-01

2023-02-24

WD>+3

57

2023-01-01

2023-02-25

WD>+3

58

2023-01-01

2023-02-26

WD>+3

59

2023-01-01

2023-02-27

WD>+3

60

2023-01-01

2023-02-28

WD>+3

vijeshkv

Cell Formulas
Range	Formula
F2:F60	F2	=IF(OR( WEEKDAY(EOMONTH(A2,0)+SUBSTITUTE(SUBSTITUTE(E2,"WD",""),">","")1)=1, WEEKDAY(EOMONTH(A2,0)+SUBSTITUTE(SUBSTITUTE(E2,"WD",""),">","")1)=7, ISNUMBER(MATCH(EOMONTH(A2,0)+SUBSTITUTE(SUBSTITUTE(E2,"WD",""),">","")1,$L$2:$L$4,0))), WORKDAY.INTL(EOMONTH(A2,0)+SUBSTITUTE(SUBSTITUTE(E2,"WD",""),">","")1,-1,1,$L$2:$L$4), EOMONTH(A2,0)+SUBSTITUTE(SUBSTITUTE(E2,"WD",""),">","")*1)

Last edited: Apr 19, 2023

Upvote 0

You must log in or register to reply here.

Similar threads

R

Solved

Is it possible for an Excel formula to calculate a sum based on date ranges?

Replies: 4

Views: 144

Nov 20, 2024

Joe4

F

Question

VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error

Replies: 2

Views: 124

Nov 24, 2024

flashgordie

F

K

Question

Populate Based on Actual and Budget

Replies: 2

Views: 96

Oct 21, 2024

kumara_faith

K

S

Solved

Networkdays formula issue

Replies: 3

Views: 354

Jul 25, 2024

Joe4

J

Solved

Daily 3yr CF: Need to flag 4th/last Thursday of each month

Replies: 3

Views: 239

Aug 19, 2024

Fluff

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,823

Messages: 6,181,173

Members: 453,021

Latest member: Justyna P

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top