Calc # of workdays in the month and # of workdays SO FAR

[montypython1]

Greetings,

What is the best way to calculate the # of workdays in a given month and the # of workdays SO FAR in that month?

I can calculate the first day of the month, the current day of the month, and the last day of the month as follows:
- lddatefirstday = DATE() - DAY(DATE())+1
- lddatecurrday = DATE()
- lddatelastday = GOMONTH(DATE() - DAY(DATE()) - 0, 1)

I know the solution is to somehow use the function DOW(), but I can't figure out how to structure the procedure. I simply want to count how many workdays there are within that range of dates. Then I want to calculate how many workdays have passed. This will enable me to accurately calculate a forecast for the month, exclusive of weekends.

Any suggestions?

Thanks,
Dave

 

[Dan Freeman]

How do you define work days?

If I normally work Saturday or Sunday is that still a workday? How do you count it? How do you account for holidays?

Counting *weekdays* is kind of easy once you've agreed on the first day of the week. *Work days* varies depending on how a work day is defined.

 

[GriffMG]

Yup and one man's weekend is another man's Friday...

Not so hard to do in principle...

Sorry, but this is probably the scrappiest bit of code ever to appear on TT

FUNCTION WORKDAYS
	PARAMETERS m.DATE1,m.DATE2
	PRIVATE m.NUMDAYS,m.DATE1,m.DATE2,m.TMPDATE,m.IDATE,m.COUNT
	PRIVATE m.INHIBITDAYS
	m.INHIBITDAYS = "SAT,SUN,"
	m.NUMDAYS = 0
	IF EMPTY(m.DATE1) .OR. EMPTY(m.DATE2)
		m.NUMDAYS = 0
	ELSE
		IF m.DATE2 < m.DATE1
			m.TMPDATE = m.DATE1
			m.DATE1 = m.DATE2
			m.DATE2 = m.TMPDATE
		ELSE
			m.TMPDATE = CTOD("//")
		ENDIF
		m.IDATE = m.DATE1
		m.COUNT = 0
		DO WHILE m.IDATE < m.DATE2
			SELECT POPHOL
			IF !UPPER(LEFT(CDOW(m.IDATE),3))$m.INHIBITDAYS
				m.COUNT = m.COUNT +1
			ENDIF
			m.IDATE = m.IDATE +1
		ENDDO
		m.NUMDAYS = m.COUNT
	ENDIF
	RETURN(m.NUMDAYS)

I would tidy it up, but I'm sure you will get the idea.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[GriffMG]

Sorry, please remove the SELECT POPHOL from the loop between dates... that's how you would get around holidays, and you didn't ask that yet.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Dan Freeman]

Yeah, accounting for holidays requires a table.

Accounting for "work days" does too, really.

And at that point you're beyond what can be done with simple date math. That's kind of my point.

 

[Mike Lewis]

Dave,

Here's my first crack at it:

ldStart = < first day of range >
ldEnd = < last day of range >
lnAnswer =
  (ldEnd - ldStart + 1) - ;
  ((WEEK(ldEnd) - WEEK(ldStart)) * 2) - ;
  IIF(DOW(ldStart) = 1, 1, 0) - ;
  IIF(DOW(ldEnd) = 7, 1, 0)

This should give the number of work days between any two dates. Just plug the appropriate values in for ldStart and ldEnd.

It assumes that work days are all days except Saturday and Sunday.

Also, the answer includes both the start and end days themselves. You might need to subtract one if that's not what you want.

I haven't tested it thoroughly, but I think it should work. If it doesn't, I've got another solution up my sleeve, but try this one first.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Sorry, but this is probably the scrappiest bit of code ever to appear on TT

I doubt it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

Thank you Mike!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

A rather simple way to determine the workdays of a month so far would be, to look up distinct dates from some table containing reported work hours or something like that, assuming people do work on work days and only on those, you'd find those days there.

If you don't have such a thing you could perhaps do somthing on each boot, depends on how people work, if they actually work at a pc or not and if they shut down and boot at all, some people I know from customers leave their PC on all the time.

Bye, Olaf.

 

[montypython1]

Hi Dan, Griff, Mike and Olaf,

Thank you all for your suggestions. I am currently in the process of implementing your ideas.

Dan and Griff, good point about my definition of the term "workdays". From my customer's point of view, "workday" means Monday through Friday (realizing that most of us tend to work weekends as well).

Again, thank you all for pointing me in the right direction.

Dave Higgins

 

[montypython1]

--- continued from earlier post ---

Mike,

Thanks again for your suggestion.

It works great, but I am struggling to get my mind around the logic (in other words, I'm amazed that you came up with the solution with so little code).

I understand counting the number of weeks in the year, and then arriving at the number of weeks within that month, but why do you multiply the result (ex: 4) by 2 ?

I also understand finding out if the starting date is on a Saturday or a Sunday, but how does it count how many Saturdays or Sundays there are if the month starts in the middle of a week (ex: Aug 2011 started on a Monday, while Sept 2011 starts on a Thursday)?

Thanks,
Dave

 

[Mike Lewis]

Dave,

I'll try to answer your questions, although this is one of those cases where it's easier to devise the logic than to explain it <g>.

I understand counting the number of weeks in the year, and then arriving at the number of weeks within that month, but why do you multiply the result (ex: 4) by 2 ?

Basically, because you are subtracting the number of weekend days, and there are two weekend days in a week.

how does it count how many Saturdays or Sundays there are if the month starts in the middle of a week

It first assumes that both the start date and the end date are weekdays. It then uses the IIF() clauses to subtract either 1 or 2 if that assumption is incorrect.

Hmm. Reading back over this reply, I'm not sure I've explained it very well. It's nearly 10.00 pm here, so I'd better not try again tonight.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

Here's a solution which will work across year boundaries (it's a variation of Griff's approach):

ldStart = < first day of range >
ldEnd = < last day of range >
lnAnswer = 0
ldI = ldStart
DO WHILE ldI <= ldEnd
  IF BETWEEN(DOW(ldI), 2, 6)
    lnAnswer = lnAnswer + 1 
  ENDIF
  ldI = ldI + 1 
ENDDO

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Mike and Griff,

Thanks again for the follow-up suggestion. I had not yet thought about what happens when the calendar year changes. Plus, I can more easily follow the logic of this new solution.

Thank you both ... I appreciate the assistance.

Dave

 

[Mike Lewis]

You're welcome, Dave.

A last thought: If you need to take account of holidays, David Aherron has a litle program that will populate a table with the relevant dates. See Generate a table of "notable dates".

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Thanks ... I'll check it out.

By the way, I took your ideas and modified it slightly so I can calculate the # of weekdays SO FAR, which then allows me to calculate the % of the month completed (for forecasting).

I always learn so much through this forum. Thank you to all who took the time to contribute (hopefully, one day, I'll know enough to be able to return the favor).

Thanks,
Dave Higgins

 

[montypython1]

Could I ask a related question? I am trying to calculate the last day of a given month, based on the first day of that month (to eliminate the user from having to enter 2 date fields). For example, if the user enters "12/01/09" as the starting date, I would like to calculate "12/31/09" as the ending date. I can accurately calculate the last day of the current month, but can't seem to calculate the last day of other months. Does you have any suggestions? (All my attempts are below).

CLEAR
ld_DOM_first = {^2009-12-01}
?"------------------------------"
?"If the value of 'ld_DOM_first' is '12/01/09' then I need the value of 'ld_DOM_last' to be '12/31/09'"
?"------------------------------"
?"ld_DOM_first= " + DTOC(ld_DOM_first)
?
?"The following is on the right track. It shows the last day of the month, but I need the last day of 'ld_DOM_first':"
ld_DOM_last = GOMONTH(DATE() - DAY(DATE()) - 0, 1) && 08/31/11
?"0 DOM_last (current month)= " + DTOC(ld_DOM_last)
?
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -0, -1) && 10/30/09
?"1 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -0, -0) && 11/30/09
?"2 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -0, +1) && 12/30/09
?"3 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) +1, -1) && 11/01/09
?"4 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) +1, -0) && 12/01/09
?"5 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) +1, +1) && 01/01/10
?"6 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -1, -1) && 10/29/09
?"7 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -1, -0) && 11/29/09
?"8 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first) -1, +1) && 12/29/10
?"9 DOM_last= " + DTOC(ld_DOM_last)
?"------------"
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -1 ) -0, -1) && 10/01/09
?"11 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -2 ) -0, -0) && 11/02/09
?"12 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first +1 ) -0, +1) && 12/29/09
?"13 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -1 ) -1, -1) && 09/30/09
?"14 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -2 ) -1, -0) && 11/01/09
?"15 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first +1 ) -1, +1) && 12/28/09
?"16 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -1 ) +1, -1) && 10/02/09
?"17 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first -2 ) +1, -0) && 11/03/09
?"18 DOM_last= " + DTOC(ld_DOM_last)
ld_DOM_last = GOMONTH(ld_DOM_first - DAY(ld_DOM_first +1 ) +1, +1) && 12/30/09
?"19 DOM_last= " + DTOC(ld_DOM_last)
?"------------------------------"
?

RETURN

 

[Dan Freeman]

Given *any* date:

ldDate = DATE()
?"Date: ", ldDate
?"First day of the month: ", (ldDate-DAY(ldDate))+1
?"Last day of the month: ", GOMONTH(ldDate,1)-DAY(ldDate)

(Note that when asking a new question it is customary to start a new thread.)

 

[montypython1]

Hi Dan,

Thanks for your solution.

Seems like I've always had a knack for taking a simple problem and making it complex. Thanks for making it simple again.

Dave