# Thread: That Date Thing Again (A2k SR1)

1. ## That Date Thing Again (A2k SR1)

I ask, how many times has this been covered ?
Any way.
What I want to do is to calculate a monthly target. To acheive this I must determine the first date of the current month & the last date of the current month. I think by using the date serial function.

Is this format like so : DateSerial(Year(Date()),Month(Date())+1,0) (I think this is correct for the last of current month)
This returns #Name?

How can I utilise the following code to achieve the amount of days less Sat & Sun.

*********** Code Start **************
Function Work_Days (BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

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") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function
'*********** Code End **************

The rest of the calculations I can acheive given that I have the above information.

Thanks
Dave

2. ## Re: That Date Thing Again (A2k SR1)

try the following

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does not account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If WeekDay(DateCnt) <> vbSunday And _
WeekDay(DateCnt) <> vbSaturday Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

this does not count the first day, but counts the last day. Use the weekday function.

3. ## Re: That Date Thing Again (A2k SR1)

Hi Dave,

The function Work_Days you posted is OK, although the version Cindy posted is more efficient (the Format function is very slow).
Your expression for finding the last day in the month is also correct. To find the number of working days in the current month in VBA:

Dim lngDaysInMonth As Long
lngDaysInMonth = Work_Days(DateSerial(Year(Date()), Month(Date()), 1), DateSerial(Year(Date()), Month(Date()) + 1, 0))

If you want to use this expression as control source of a text box, place = before it:

=Work_Days(DateSerial(Year(Date()), Month(Date()), 1), DateSerial(Year(Date()), Month(Date()) + 1, 0))

4. ## Re: That Date Thing Again (A2k SR1)

<P ID="edit" class=small>(Edited by D Willett on 11-Oct-02 11:22. )</P>(Hans, i think we posted at the same time)

Thanks Cindy but I need to go one step at a time.
I have two unbound texts on a form.
In the first text I want the first date (short format) of the current month.

In the second I want the last date of the current month.

My DateSerial doesn't work and returns #Name?.

I put the statement in the control source.

Regards Dave

5. ## Re: That Date Thing Again (A2k SR1)

Dave,

I would do something like the following:

Given dteCurrent is the day in the month you want to find the start of month and end of month for.

dteStartofMonth = date(year(dtecurrent),month(dtecurrent),01)

the end of the month deducts 1 day from the start of the next month

Hope this helps

6. ## Re: That Date Thing Again (A2k SR1)

Ok
I copied Cindy's code into a new module and applied the +Dateserial from hans and now returning a date.
Problem is it returns 21/01/00 instead of the current month.
Is there a format problem here

Thanks
Dave

7. ## Re: That Date Thing Again (A2k SR1)

Slow down, we seem to be posting at the same time, I'll catch up in a minute.

Thanks
Dave

8. ## Re: That Date Thing Again (A2k SR1)

See the attached ??

9. ## Re: That Date Thing Again (A2k SR1)

Dave,

I am unsure what is wrong, please give an explanation what is wrong and I can then have a look. Better still attach the/a mdb with the code in and I can do any alterations required.

10. ## Re: That Date Thing Again (A2k SR1)

You can calculate the last day of the current month like this:

Note that it will need tweaking to handle February 29 and the last day of months with 31 days.

11. ## Re: That Date Thing Again (A2k SR1)

Thanks Hans & Charlotte for the input.
Hope I didn't offend any one with the last post. I was getting confused.
Anyway.

Cindy I've attached as asked but not included the code, I thought I would leave that to your best judgment.

Once again thanks everyone.
regards
Dave

12. ## Re: That Date Thing Again (A2k SR1)

(Charlotte you function works for me,Thanks)

I seem to be getting somewhere now.

"First of month" =DateAdd("d",1-DatePart("d",Date()),Date())
"Last of month" =DateSerial(DatePart("yyyy",Date()),DatePart("m",D ate())+1,1)-1

Me.txtResult.Value = Work_Days(Me.FirstOfMonthDate.Value, Me.LastOfMonthDate.Value)

I have this working now but one little thing, There are 23 work days in october but the code returns 22.
If I change the system date to November, the correct number appears 21 ??

Dave

13. ## Re: That Date Thing Again (A2k SR1)

Thanks to all, Now sorted.

I had another module which also takes off pre-determined holidat dates from tblHolidays
basHolidayDatesWorkDays

I had it some time ago from Hans.

Again, Thanks To All

Dave

14. ## Re: That Date Thing Again (A2k SR1)

This function seems to find the last day of the current month without needing any tweaking:
=DateSerial(Year(Date()),Month(Date())+1,1)-1

It finds the first day of the next month, then comes back one day.

15. ## Re: That Date Thing Again (A2k SR1)

A slight variation on this is:
=DateSerial(Year(Date()),Month(Date())+1,0)
Note that either of these work reliably for all months.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•