>Hi,
>
>I am writing the code in C# but if I know how to do it in VFP, I can easily convert it to C#. That is, I know C# but weak in math :). And I can do this with a bunch of IF CASE and so on. But I think there should be a more concise formula.
>
>nTodayDow - today/current day Day of the week (1 to 7)
>nDays2Advance - variable that holds the number of days to advance, from the current day. This can be 1 to any reasonable number (e.g. 100). And I want to change the nDays2Advance given that the weekend should be skipped (Saturday and Sunday).
>Example:
>nTodayDow = 3 (Wednesday)
>nDays2Advance = 2
>So the nDays2Advance = 2 (since the weekend is too far ahead)
>But if nDays2Advance is 4, then it should be changed to 6, otherwise it will fall on the weekend.
>
>How can I express the above in the formula that would help me calculate nDays2Advance?
>
>TIA

Dmitry, this a more comprehensive reply that considers holidays and continues to avoid day-by-day loops. It rests on the shoulders of Dragan's idea to use a cursor for holidays and base some kind of check on that. What this does not cover is the possibility of different working weeks (for instance, one in which the Friday is a rest day), not to mention irregular business weeks.

The code is at the bottom, and it's decorated with "?" to show things going on. The start date and the working days to advance are hard coded, because these particular values will be useful to illustrate the inner workings of the program.

It starts on the last Friday, 8th of December, to which one must add 25 business days.

Without considering weekends, adding 25 days moves {^2017-12-08} to {^2018-01-02} (a Tuesday). But, because of the weekends occurring in between, the 25 business days correspond to 35 calendar days. That calculation uses the simple formula we fine tuned elsewhere in this thread.

So, the first adjustment moves the end date from {^2018-01-02} to {^2018-01-12} (a Friday),

Now, for the holidays. I'm using the Calendar class at https://bitbucket.org/atlopes/calendar, to which I added today a EventsToCursor() method, to match Dragan's suggestion. The holidays are American National holidays. As a safety net, I consider the events of not only the start date year and the end date year, but also of the year after.

The test for holidays is executed by counting, with a single SELECT, the number of holidays falling on business days that occur in a period, which is initially set by the start date, and the end date.

There are two holidays between the start date and the end date (Christmas and New Year), so we'll have to advance our end date two more days (that is, {^2018-01-14}). Because that would fall on a weekend, we have to push further two more days. So, our 35 days to advance become 39, and our new end date falls on {^2018-01-16} (this, once again, is calculated using the same formula above).

Now, we pick the day after the previous end date ({^2018-01-12}) to restart the period for holidays (always terminating at the current end date). And since there is another holiday, which is the Birthday of Martin Luther King (in 2018, set for {^2018-01-15}), we must advance another day (1 holiday = 1 day to advance). This time, since we don't pass over weekends, that is all we have to add, going from 39 to 40 to move the end date by 1 day to {^2018-01-17}.

The test repeats: get the day after the previous end date ({^2018-01-17}) and the current end date (the same {^2018-01-17}), and check if there is any holiday in this 1-day period. Since there isn't, the test ends.

The initial 25 days advance turned out to be 40 (due to 6 weekends and 3 holidays), and our end date now falls on a Wednesday.

The code:

DO LOCFILE("gregorian-calendar.prg")
DO LOCFILE("us-calendar-events.prg")

LOCAL Cal AS GregorianCalendar
LOCAL StartDate AS Date
LOCAL EndDate AS Date
LOCAL TestDate AS Date
LOCAL WorkingDaysToAdvance AS Integer
LOCAL CalendarDaysToAdvance AS Integer
LOCAL StartYear AS Integer
LOCAL EndYear AS Integer
LOCAL YearsToConsider AS Integer
LOCAL TestHolidays AS Boolean
LOCAL HolidaysInPeriod AS Integer

* where we are, how much we want to advance
m.StartDate = {^2017-12-08}
m.WorkingDaysToAdvance = 25

SET CENTURY ON
SET DATE ANSI

CLEAR

? "Start date:", m.StartDate FONT "Arial" STYLE "B"
? "Days to advance:", m.WorkingDaysToAdvance FONT "Arial" STYLE "B"
?

m.EndDate = m.StartDate + m.WorkingDaysToAdvance
? "Initial end date:", m.EndDate

m.CalendarDaysToAdvance = m.WorkingDaysToAdvance + INT((DOW(m.StartDate, 2) + m.WorkingDaysToAdvance - 1) / 5) * 2

? "Days to advance, adjusted to weekends:", m.CalendarDaysToAdvance
m.EndDate = m.StartDate + m.CalendarDaysToAdvance
? "New end date:", m.EndDate

* prepare a Calendar class to determine the holidays
m.Cal = CREATEOBJECT("GregorianCalendar")
m.Cal.FromSystem(m.StartDate)
m.StartYear = m.Cal.Year
m.Cal.FromSystem(m.EndDate)
m.EndYear = m.Cal.Year + 1

m.Cal.AttachEventProcessor("us", "USCalendarEvents")

* get a list of events in the years to come (just to be safe)
FOR m.YearsToConsider = m.StartYear TO m.EndYear
	m.Cal.SetEvents(m.YearsToConsider)
	IF m.YearsToConsider = m.StartYear
		m.Cal.EventsToCursor("holidays")
	ELSE
		m.Cal.EventsToCursor("temp")
		INSERT INTO holidays SELECT * FROM temp
		USE IN temp
	ENDIF
ENDFOR

m.TestHolidays = .T.
* the initial test period covers all advanced days, so far
m.TestDate = m.StartDate + 1

DO WHILE m.TestHolidays

	* get the holidays in the current test period
	SELECT DISTINCT(SystemDate) ;
		FROM holidays ;
		WHERE SystemDate BETWEEN m.TestDate AND m.EndDate ;
			AND DOW(SystemDate, 2) < 6 ;
			AND Observed AND Scope == "National" ;
		INTO CURSOR HolidaysInPeriod

	m.HolidaysInPeriod = RECCOUNT("HolidaysInPeriod")
	? "Holidays in the period:", m.HolidaysInPeriod

	* if there are holidays in the period
	IF m.HolidaysInPeriod != 0
	
		* prepare the next test period
		m.TestDate = m.EndDate + 1
		* advance considering holidays but also weekends
		m.CalendarDaysToAdvance = m.CalendarDaysToAdvance + m.HolidaysInPeriod + INT((DOW(m.EndDate, 2) + m.HolidaysInPeriod - 1) / 5) * 2
		
		? "Days to advance, adjusted to holidays:", m.CalendarDaysToAdvance
		m.EndDate = m.StartDate + m.CalendarDaysToAdvance
		? "New end date:", m.EndDate
	ELSE
		* no more holidays, we are done
		m.TestHolidays = .F.
	ENDIF
ENDDO

?
? "Final end date:", m.EndDate FONT "Arial" STYLE "B"
? "Total days advanced:", m.CalendarDaysToAdvance FONT "Arial" STYLE "B"