How do I modify this function ? 2

[testkitt2]

Good evening to all.

This code was provide by Tek-Tips "TheAceman1" save me a lot of time and grief.
Could I ask if someone could help me modify this code to calculate the number of working days in a given month. For example this month "March" has 24.5 working days, We count sat as a half a day. The code below give you the amount of days that are left in a month. Any help is greatly appreciated.

 Function Work_Days(BegDate As Variant, endDate As Variant) As Single
   ' Note that this function does not account for holidays.
     Dim WholeWeeks As Variant
      Dim DateCnt As Variant
      Dim EndDays As Single
      
   
      BegDate = DateValue(BegDate)
      endDate = DateValue(endDate)
      WholeWeeks = DateDiff("w", BegDate, endDate)
      DateCnt = DateAdd("ww", WholeWeeks, BegDate)
      EndDays = 0
      
      Do While DateCnt < endDate
         If Format(DateCnt, "ddd") = "Sat" Then
            EndDays = EndDays + 0.5
         ElseIf Format(DateCnt, "ddd") <> "Sun" Then
            EndDays = EndDays + 1
         End If
         DateCnt = DateAdd("d", 1, DateCnt)
      Loop
      
      Work_Days = WholeWeeks * 5.5 + EndDays
   End Function

Testkitt2

 

[AlexCuse]

You could either pass in a start and end date of the month, or modify it to accept only a month name.

I would just code your app so that you use this same function, but pass it start and end dates for a month. Then you only need to get the first and last dates of the month to pass in (hint: you can get these by using the DateAdd function, user will just need to pass month and year).

Assuming month is entered in txtMnth and year in txtYear, something like this may work (not tested, you might need to use explicit conversions to date before using DateAdd)

Either way, this should get you started:

dim stDate as String
dim enDate as String
dim wrkDayCnt as Single

stDate = txtMnth.text & "/01/" & txtYear.text
enDate = DateAdd("d", -1, DateAdd("m", +1, stDate))

wrkDayCnt = Work_Days(stDate, enDate)

Notice what the DateAdd is doing, it is taking the day before the date that we get by adding one month to the start date (in other words, the last date of the month in question)

Hope this helps,

Alex



Ignorance of certain subjects is a great part of wisdom

 

[testkitt2]

Hey thanks AlexCuse for your suggestion.
I'm just not that good.
I tried to use it but got nothing.
I've been trying to modify the existing code, but I'm not
getting the right results.
Again the existing code is being used to Calc number of workdays left in the month. I need to know how many workdays have gone by.
Thank you
Testkitt2

Testkitt2

 

[misterstick]

non-optimal, ottomh:

Public Function WorkingDays(datStart As Date, datEnd As Date) As Single
Dim datStartTemp As Date
Dim datEndTemp As Date
Dim sngNumberOfDays As Single
datStartTemp = DateSerial(Year(datStart), Month(datStart), Day(datStart))
datEndTemp = DateSerial(Year(datEnd), Month(datEnd), Day(datEnd))
For d = datStartTemp To datEndTemp
  Select Case d Mod 7
    Case 2 To 6
      sngNumberOfDays = sngNumberOfDays + 1
    Case 0
      sngNumberOfDays = sngNumberOfDays + 0.5
    Case Else
  End Select
Next
WorkingDays = sngNumberOfDays
End Function

mr s. <

 

[MichaelRed]

misterstick is close, but the start and end date arguments would have to acrually BE the first and Last Dates of interest (e.g. 3/1/07 and 3/31/07 for march of theis year). If this is so, the internal variables (datStartTemp & datEndTemp) are simply redundant (hmmm ... actually they are anyway ... ther is no modification to the input vars to generate the internal vars).

Replacing these two calcs to ALWAYS return the "working days" in a month"

datStartTemp = DateSerial(Year(datStart, MOnth(datStart), 0)
datEndTemp = DateSerial(Year(datStart, MOnth(datStart) + 1, 0)


Would fix the function FOR ALWAYS returning the number of "working days" in a month, but leave the vexing issue of having "broken" the function for its' original purpose - thus potentially creating more problems where ever (else) it is used. Beyond this, the dunction does not accouunt for holidays, so leaves the potential for other anamolies in the overall calculation for business purposes.

The faq section has a couple of entries which DO provide for the exclusion of holidays and appear (overall) to be much more robust than either of the procedures proposed here, although the inclusion of using saturday as a 1/2 working day would still need to be implemented.




MichaelRed

 

[testkitt2]

Hello to all ...
MichaelRed
Thank you for your suggestion, I'm going to insert a new module and copy existing code and alter it a bit to see if it works.

My objective is this: If you have 24.5 working days (calc by a function) and 8.5 days have already passed..(also a function to calc days passed.
If you had a $25,000 monthly budget to work with, and you are spending $650 daily, you are spending $76.47 daily.

Now...what I'm trying to do is forcast based on the above what will I spend by the end of the month.
Shouldn't it be $650 div by 8.5 multi by 24.5 to give you a forcast?

Thanks to all
Testkitt2

Testkitt2

 

[misterstick]

um, the temp variables are there because dates also possibly (generally?) contain the fractional time portion.

it's just easier if you know you've got whole number dates.





mr s. <

 

[testkitt2]

Hello to all
Thank you misterstick for your response.
The reason for half days is saturday...

we work only half days on sat...off on sunday ...then work monday thru friday whole days.
Thank you
Testkitt2

Testkitt2