MS Acces vba function to get thursday as first day of week in current month and Wednesday as last week in current month.

[ashfaqahmedr]

Function for get Thursday as first day of week in current week month, this function returning first week as Thursday but first week of month in last month, But K need it will return only current month. For example if 1st date of month is on Tuesday it will return 1st date of month.


Function GetFirstofWeek(dtDate) As Date

'=MAX(C3-WEEKDAY(C3,14)+1, EOMONTH(C3, -1)+1)
GetFirstofWeek = DateAdd("d", dtDate, -Weekday(dtDate, vbThursday) + 1)
End Function
Function for get Wednesday as last day of week in current week month, this function returning last week as Wednesday but last week of month in next month, But I need it will return only current month. For example if last date of month is on Monday it will return 30th date of current month.
Function GetLastofWeek(dtDate As Date)
'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))
GetLastofWeek = DateAdd("d", dtDate, (7 - (Weekday(dtDate, vbWednesday)) + 1))
End Function
This function will return last date of month
Public Function EOMonth(RefDt As Date, Optional Months As Integer = 0) As Date
On Error GoTo Error_Handler
'DateSerial(Year([TxtDate]),Month([TxtDate])+1,0)

EOMonth = DateSerial(Year(RefDt), Month(RefDt) + Months + 1, 0)
End Function

This function will return first date of month
Public Function SOMonth(RefDate As Date, Optional Months As Integer = 0) As Date
On Error GoTo Error_Handler
'DateSerial(Year([TxtDate]),Month([TxtDate])-0,0)+1
SOMonth = DateSerial(Year(RefDate), Month(RefDate) + Months - 0, 0) + 1

 

[welshgasman]

The WEEKDAY function already has a parameter to specify the first day of a week?

https://www.excelfunctions.net/vba-weekday-function.html

 

[ashfaqahmedr]

The WEEKDAY function already has a parameter to specify the first day of a week?

https://www.excelfunctions.net/vba-weekday-function.html

But it retuns last month first day of week as thursday.

And also in last day of week it returns last day as wednesday in next month.

 

[welshgasman]

What I was suggesting was using that parameter, could cut down on all the math you have you do if the first day is Sunday/Monday?

 

[ashfaqahmedr]

What I was suggesting was using that parameter, could cut down on all the math you have you do if the first day is Sunday/Monday?

I have tried it,but not work. Can I send you ms access database.

 

[welshgasman]

No, I am not sure even what you want from your description.?
I *think* you are looking for the first Thursday in the current month, and the last Wednesday in the current month?
If so I would just get the weekday of the first and last days of current month and then add subtract accordingly the number of days.?
Weekday of first of month

weekday(dateserial(Year(date),month(date),0)+1)

Weekday of last of month

weekday(dateserial(Year(date),month(date)+1,0)

then do the math for your Thursday or Wednesday.

HTH

 

[ashfaqahmedr]

No, I am not sure even what you want from your description.?
I *think* you are looking for the first Thursday in the current month, and the last Wednesday in the current month?
If so I would just get the weekday of the first and last days of current month and then add subtract accordingly the number of days.?
Weekday of first of month

weekday(dateserial(Year(date),month(date),0)+1)

Weekday of last of month

weekday(dateserial(Year(date),month(date)+1,0)

then do the math for your Thursday or Wednesday.

HTH

Yes offcourse I want to get this out put

Week First day (Thur) week last day (Wed)

1/10/2018 03/10/2018
4/10/2018 10/10/2018
11/10/2018 17/10/2018
18/10/2018 24/10/2018
25/10/2018 31/10/2018

Functions I have created for first day of week and last day of week are returning dates as follow:
Week First day (Thur) week last day (Wed)

27/09/2018 to 03/10/2018
4/10/2018 10/10/2018
11/10/2018 17/10/2018
18/10/2018 24/10/2018
25/10/2018 31/10/2018
And if last week of month is in next month then like first week of Oct-18 contains these dates 27-09-2018 to 03-10-2018, I want divide this week as 27-09-2018 to 30-09-2018 and from 01-10-2018 to 03-10-2018.

Can you suggest me any correction.

I have done this excel throug weekday and eomonth function which is also quoted in firstofweek and lasofweek functions for better understanding and reference.

 

[welshgasman]

No.
Not without a lot of code.
You would need to calculate the weekdays of first and last of month, which you appear to have.
If not exact days (Oct last day is a Wednesday) then do the math, then work out you have gone into a different month, then adjust for that.
How you are meant to return these weeks (could be up to 6?) I do not know, array perhaps.?

 

[ashfaqahmedr]

I have tried your code for first and last day of week it return 01/01/1900.

 

[ashfaqahmedr]

I have done it with Excel formula for First day of week "'=MAX(C3-WEEKDAY(C3,14)+1, EOMONTH(C3, -1)+1)"
and last day of week "'=MIN(B2+(7-WEEKDAY(B2,14)), EOMONTH(B2, 0))"