convert working day to day of month

[Merilyn]

what formula is used in access to convert the working day to day of current month, meaning if your given working day of the month how to convert it to day in current month, for example the current month is march so:



1st working day => 3/1/18
2nd working day => 3/2/18
3rd working day => 3/3/18
4th working day => 3/5/18 #here we skipped 3/4/18 because it is a sunday
5th working day => 3/6/18
etc...

how do you do this in access automatically using a query or vba ?

[ArviLaanemets]

Create a calendary table, p.e.
tCalendary:CalendaryDate, Type (p.e. with values 1 - workday, 2 - weekend, 3 - holyday, etc), ... (you can have another fields like MonthNo (in format YYYYMM), WeekNo (in format YYYYWW), WrkHrs, etc.).

Fill the table for some amount of years into past and into future.

Use this table whenever you need to determine, is the date a workday, or which dates of some period are workdays. Or whatever other info from there you need.

[Merilyn]

ArviLaanemets,

I am sorry am confused, so what you are saying is to create a table as follows

tblCalendar

CalendarDate	Type
3/1/18	workday
3/2/18	workday
3/3/18	workday
3/4/18	weekend
3/5/18	workday
3/6/18	workday
3/7/18	workday
3/8/18	workday
3/9/18	workday

but how to use that to convert the following table to calendar date:

working day
1
2
3
4
5
6
7
8
9

There is no common field between the two tables, how can I use the first table to convert the second table ?

[Micron]

how do you do this in access automatically using a query or vba ?

The reason you were advised to create a table is because there is no standard for what constitutes a working day. For example, you labelled 3/03/2018 as a working day. For many, Saturday is not a work day. There is also no consistency in what constitutes a holiday; not even across some countries, let alone the world. So by creating a table specific to your needs, you denote/mark which are and which are not. All you need is a field for dates and another field that specifies yes or no. This field should not be named Type. This can be a yes/no true/false on/off, numeric (0 or -1) or text. To get the numeric value for the date, all you need is to use a date function, such as DatePart to extract 2 from 03/02/2018

There is no need to store this value in a table. To do so would be redundant as the value is already contained within the date. It should be represented on a form or report as a calculated field that uses a function such as the one I mentioned.

[isladogs]

Whilst agreeing with the points just made, I think both answers have missed the point that the OP requested.

Having determined which days are working days, the OP wants to know the date for e.g the 9th, 15th, 23rd etc working day in each month
So a rank order needs to be applied as well
For info on one way of doing this using the serialize function see my post here https://www.access-programmers.co.uk...ght=Rank+order
Alternatively use a subquery as explained in this link http://allenbrowne.com/ranking.html

[ArviLaanemets]

For OP's current question, to calculate n'th workday of given month, he needs to query a recordset from table tCalendary for given month with workday DateType and ordered by CalendaryDate, and get a date from n'th record from this recordset. To do this, he needs either a function used p.e. as formula in some unbound control, or an event for some control, which calculates the date and writes the result into some text box,

[Micron]

I think what's needed is a clear and concise explanation of the desired result, complete with sample data rather than asking how to connect things which seem to have more than one possible purpose. I can't disagree with any of the suppositions being made by anyone, but we seem to be the only ones posting now. I'm going to wait for clarification.

[orange]

I agree that the requirement is not clear. The OP should restate the issue giving a fuller description and possibly some examples.

What is the typical work week?
What is first day of work week and last day of work week?
What are the relevant holidays?


@Merilyn,
Here is a link to the code repository where I added a routine dealing with Business Days /Workdays). The code and comments may be useful.
Good luck.