Convert day of month to Business Day date

AFlint

We have a calendar template that we re-use every month. On this calendar ,we have a column that is the numerical representation of the day of the month (1, 2, 3, 4, etc.). Each month, this is a different date. Currently, someone is manually entering the dates that correspond to the day number.

Is there a formula that would convert 1 to the first business day, 2 to the second business day, etc.?

Jgorsich

So you basically need day() except it shouldn't increment on weekends, right? So using May 2025 as an example,

5/1/25 = 1

5/2/25 = 2

5/5/25 = 3

right?

In addition, I presume you want to skip holidays also…

Networkdays() using the last day of the previous month and the date you are evaluating (possibly including an offset of 1, haven't tested it) for the start and end date seems like it should work for you.

NETWORKDAYS Function | Smartsheet Learning Center

Paul Newcome

Can you provide a screenshot for context?

AFlint

@Paul Newcome
Here is a sample calendar from March. I included the Gantt to show the distribution across weekdays (this is how they are currently checking themselves manually)
Essentially, the goal would be to start with "Business Day" and a helper cell for the current month an have the formula calculate "Date"

Jgorsich

If you want to derive BusinessDay from Date, the method I suggested originally should work:

=networkdays(date@row-day(date@row)-1,date@row)

if you want to derive Date from BusinessDay, workday() should work:

=workday(today()-day(today())+1,BusinessDay@row)

Both also allow you to address holidays if you are so inclined.

Paul Newcome

Start by inserting a Sheet Summary date type field. This field will be where you set the month for the calendar. You can enter any date of the month (the formula will make it so you don't need to worry about selecting a specific date so long as it is in the desired month/year).

Then this should get your dates in the Date column:

=WORKDAY(DATE(YEAR([Date Summary Field]#), MONTH([Date Summary Field]#), 1), [Business Day]@row-1)