Return calendar date based on nth business day?

[megera716]

I have a list of month-end closing tasks with the business day of the month a given task should be completed by, as in 1st, 2nd, 3rd.

Can someone come up with a formula that would tell me what calendar date that corresponds to? I'm happy to put a date in a random cell somewhere as a helper.

For example, when we were closing September, a "1" for "Due Business Day #" would return 10/3/2022. For October's close in November, the 5th business day is 11/7/22. Day 0 is the last day of the prior month (e.g., these are closing activities that can actually be done by/before the last day of that calendar month so for October's close that would be 10/31/22). A number with a decimal was more for sorting (I can't approve the payroll journal entries entered by my accounting specialist until she enters them (by day 0) so I put 0.1. The calendar date would be the same as the "whole number" date).

 

[Dave Patton]

What are your business days?
Can you show some examples with expected results?

 

[megera716]

What are your business days?
Can you show some examples with expected results?

Business days are standard Monday-Friday. The only holidays that will affect closing timelines are New Years and possibly Labor Day but I can deal with those one-off.

I described the end goal in my post -- should I have populated the example of my spreadsheet instead?

 

[Dave Patton]

You can try Workday function or Workday.intl

Workday.xlsm
	A	B	C
1	Wed 05-Oct-22	1	Mon 03-Oct-22
2	Fri 30-Sep-22	1	Mon 03-Oct-22
3	Tue 01-Nov-22	1	Tue 01-Nov-22
4	Mon 31-Oct-22	1	Tue 01-Nov-22
5	Mon 31-Oct-22	5	Mon 07-Nov-22
6
3d

Cell Formulas
Range	Formula
A3	A3	=TODAY()
C1,C3	C1	=WORKDAY(EOMONTH(A1,-1),B1)
C2,C4:C5	C2	=WORKDAY(A2,B2)

 

[megera716]

Ahh! PERFECT, thank you!