Work Day Calendar

Vijeshkv

New Member
Joined
Aug 7, 2018
Messages
12
Hello ,

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

Posting MonthEntry DatePosting Month_Entry DateWeek DayWD DayWorkday GroupPeriodQuarter Close/Non QtrUS Public Holiday
1/1/2023​
1/1/2023​
44927_44927SundayWD-21Non Month Close
1​
Non Quarter End
1/1/2023​
1/2/2023​
44927_44928MondayWD-21Non Month Close
1​
Non Quarter EndNew Year’s: Monday, January 2
1/1/2023​
1/3/2023​
44927_44929TuesdayWD-20Non Month Close
1​
Non Quarter End
1/1/2023​
1/4/2023​
44927_44930WednesdayWD-19Non Month Close
1​
Non Quarter End
1/1/2023​
1/5/2023​
44927_44931ThursdayWD-18Non Month Close
1​
Non Quarter End
1/1/2023​
1/6/2023​
44927_44932FridayWD-17Non Month Close
1​
Non Quarter End
1/1/2023​
1/7/2023​
44927_44933SaturdayWD-16Non Month Close
1​
Non Quarter End
1/1/2023​
1/8/2023​
44927_44934SundayWD-16Non Month Close
1​
Non Quarter End
1/1/2023​
1/9/2023​
44927_44935MondayWD-16Non Month Close
1​
Non Quarter End
1/1/2023​
1/10/2023​
44927_44936TuesdayWD-15Non Month Close
1​
Non Quarter End
1/1/2023​
1/11/2023​
44927_44937WednesdayWD-14Non Month Close
1​
Non Quarter End
1/1/2023​
1/12/2023​
44927_44938ThursdayWD-13Non Month Close
1​
Non Quarter End
1/1/2023​
1/13/2023​
44927_44939FridayWD-12Non Month Close
1​
Non Quarter End
1/1/2023​
1/14/2023​
44927_44940SaturdayWD-11Non Month Close
1​
Non Quarter End
1/1/2023​
1/15/2023​
44927_44941SundayWD-11Non Month Close
1​
Non Quarter End
1/1/2023​
1/16/2023​
44927_44942MondayWD-11Non Month Close
1​
Non Quarter EndMartin Luther King Jr.: Monday, January 16
1/1/2023​
1/17/2023​
44927_44943TuesdayWD-11Non Month Close
1​
Non Quarter End
1/1/2023​
1/18/2023​
44927_44944WednesdayWD-10Non Month Close
1​
Non Quarter End
1/1/2023​
1/19/2023​
44927_44945ThursdayWD-9Non Month Close
1​
Non Quarter End
1/1/2023​
1/20/2023​
44927_44946FridayWD-8Non Month Close
1​
Non Quarter End
1/1/2023​
1/21/2023​
44927_44947SaturdayWD-7Non Month Close
1​
Non Quarter End
1/1/2023​
1/22/2023​
44927_44948SundayWD-7Non Month Close
1​
Non Quarter End
1/1/2023​
1/23/2023​
44927_44949MondayWD-7Non Month Close
1​
Non Quarter End
1/1/2023​
1/24/2023​
44927_44950TuesdayWD-6Non Month Close
1​
Non Quarter End
1/1/2023​
1/25/2023​
44927_44951WednesdayWD-5Month Close
1​
Non Quarter End
1/1/2023​
1/26/2023​
44927_44952ThursdayWD-4Month Close
1​
Non Quarter End
1/1/2023​
1/27/2023​
44927_44953FridayWD-3Month Close
1​
Non Quarter End
1/1/2023​
1/28/2023​
44927_44954SaturdayWD-2Month Close
1​
Non Quarter End
1/1/2023​
1/29/2023​
44927_44955SundayWD-2Month Close
1​
Non Quarter End
1/1/2023​
1/30/2023​
44927_44956MondayWD-2Month Close
1​
Non Quarter End
1/1/2023​
1/31/2023​
44927_44957TuesdayWD-1Month Close
1​
Non Quarter End
1/1/2023​
2/1/2023​
44927_44958WednesdayWD+1Month Close
1​
Non Quarter End
1/1/2023​
2/2/2023​
44927_44959ThursdayWD+2Month Close
1​
Non Quarter End
1/1/2023​
2/3/2023​
44927_44960FridayWD+3Month Close
1​
Non Quarter End
1/1/2023​
2/4/2023​
44927_44961SaturdayWD+3Month Close
1​
Non Quarter End
1/1/2023​
2/5/2023​
44927_44962SundayWD+3Month Close
1​
Non Quarter End
1/1/2023​
2/6/2023​
44927_44963MondayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/7/2023​
44927_44964TuesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/8/2023​
44927_44965WednesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/9/2023​
44927_44966ThursdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/10/2023​
44927_44967FridayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/11/2023​
44927_44968SaturdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/12/2023​
44927_44969SundayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/13/2023​
44927_44970MondayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/14/2023​
44927_44971TuesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/15/2023​
44927_44972WednesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/16/2023​
44927_44973ThursdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/17/2023​
44927_44974FridayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/18/2023​
44927_44975SaturdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/19/2023​
44927_44976SundayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/20/2023​
44927_44977MondayWD>+3Post Month Close
1​
Non Quarter EndPresident’s Day: Monday, February 20
1/1/2023​
2/21/2023​
44927_44978TuesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/22/2023​
44927_44979WednesdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/23/2023​
44927_44980ThursdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/24/2023​
44927_44981FridayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/25/2023​
44927_44982SaturdayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/26/2023​
44927_44983SundayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/27/2023​
44927_44984MondayWD>+3Post Month Close
1​
Non Quarter End
1/1/2023​
2/28/2023​
44927_44985TuesdayWD>+3Post Month Close
1​
Non Quarter End
 

Attachments

  • 1681886260775.png
    1681886260775.png
    141.4 KB · Views: 17

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
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
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
This we basically uses for Accounting Calendars
Entry date is used for calculating the WD
Posting month and Entry date use
 
Upvote 0
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
ABC
1
2WD+24
3
Sheet3
Cell Formulas
RangeFormula
B2B2=INDEX("WD" & CHOOSE(RANDARRAY(1,30,1,2,1),"-","+")&RANDARRAY(1,30,1,30,1),RANDARRAY(1,1,1,30,1))
 
Last edited:
Upvote 0
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
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
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
ABCDEFGH
1Posting MonthEntry DateWeek DayWD DayAdj DaycalculationcommentHOLIDAY SCHEDULE
22023-01-012023-01-01SundayWD-21-212022-12-2921 days before Jan Monthend?2023-01-02
32023-01-012023-01-02MondayWD-21-212022-12-292023-01-16
42023-01-012023-01-03TuesdayWD-20-202022-12-302023-02-20
52023-01-012023-01-04WednesdayWD-19-192023-01-03
62023-01-012023-01-05ThursdayWD-18-182023-01-04
72023-01-012023-01-06FridayWD-17-172023-01-05
82023-01-012023-01-07SaturdayWD-16-162023-01-06
vijeshkv
Cell Formulas
RangeFormula
E2:E8E2=SUBSTITUTE(SUBSTITUTE(D2,"WD",""),">","")*1
F2:F8F2=WORKDAY.INTL(EOMONTH(A2,0),E2,1,$H$2:$H$4)
 
Upvote 0
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
ABCDEFGHIJKL
1Posting MonthEntry DatePosting Month_Entry DateWeek DayWD DaycalculationPeriodWorkday GroupPeriodQuarter Close/Non QtrUS Public HolidayHOLIDAY SCHEDULE
22023-01-012023-01-01WD-212023-01-102023-01-02
32023-01-012023-01-02WD-212023-01-102023-01-16
42023-01-012023-01-03WD-202023-01-112023-02-20
52023-01-012023-01-04WD-192023-01-12
62023-01-012023-01-05WD-182023-01-13
72023-01-012023-01-06WD-172023-01-13
82023-01-012023-01-07WD-162023-01-13
92023-01-012023-01-08WD-162023-01-13
102023-01-012023-01-09WD-162023-01-13
112023-01-012023-01-10WD-152023-01-13
122023-01-012023-01-11WD-142023-01-17
132023-01-012023-01-12WD-132023-01-18
142023-01-012023-01-13WD-122023-01-19
152023-01-012023-01-14WD-112023-01-20
162023-01-012023-01-15WD-112023-01-20
172023-01-012023-01-16WD-112023-01-20
182023-01-012023-01-17WD-112023-01-20
192023-01-012023-01-18WD-102023-01-20
202023-01-012023-01-19WD-92023-01-20
212023-01-012023-01-20WD-82023-01-23
222023-01-012023-01-21WD-72023-01-24
232023-01-012023-01-22WD-72023-01-24
242023-01-012023-01-23WD-72023-01-24
252023-01-012023-01-24WD-62023-01-25
262023-01-012023-01-25WD-52023-01-26
272023-01-012023-01-26WD-42023-01-27
282023-01-012023-01-27WD-32023-01-27
292023-01-012023-01-28WD-22023-01-27
302023-01-012023-01-29WD-22023-01-27
312023-01-012023-01-30WD-22023-01-27
322023-01-012023-01-31WD-12023-01-30
332023-01-012023-02-01WD+12023-02-01
342023-01-012023-02-02WD+22023-02-02
352023-01-012023-02-03WD+32023-02-03
362023-01-012023-02-04WD+32023-02-03
372023-01-012023-02-05WD+32023-02-03
382023-01-012023-02-06WD>+32023-02-03
392023-01-012023-02-07WD>+32023-02-03
402023-01-012023-02-08WD>+32023-02-03
412023-01-012023-02-09WD>+32023-02-03
422023-01-012023-02-10WD>+32023-02-03
432023-01-012023-02-11WD>+32023-02-03
442023-01-012023-02-12WD>+32023-02-03
452023-01-012023-02-13WD>+32023-02-03
462023-01-012023-02-14WD>+32023-02-03
472023-01-012023-02-15WD>+32023-02-03
482023-01-012023-02-16WD>+32023-02-03
492023-01-012023-02-17WD>+32023-02-03
502023-01-012023-02-18WD>+32023-02-03
512023-01-012023-02-19WD>+32023-02-03
522023-01-012023-02-20WD>+32023-02-03
532023-01-012023-02-21WD>+32023-02-03
542023-01-012023-02-22WD>+32023-02-03
552023-01-012023-02-23WD>+32023-02-03
562023-01-012023-02-24WD>+32023-02-03
572023-01-012023-02-25WD>+32023-02-03
582023-01-012023-02-26WD>+32023-02-03
592023-01-012023-02-27WD>+32023-02-03
602023-01-012023-02-28WD>+32023-02-03
vijeshkv
Cell Formulas
RangeFormula
F2:F60F2=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:
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top