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

ashfaqahmedr

New Member
Joined
Oct 4, 2018
Messages
8
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
 
From the debug window
Code:
? dateserial(Year(date),month(date)+1,0)
31/10/2018 
? dateserial(Year(date),month(date),1)
01/10/2018

Why are you trying formulae in Excel when you want an Access function and this is an Access forum?

I would get the weekday of the first of the month.
If not a Thursday, calculate the date for the previous Thursday and then use last date of that month.
That is your first week.
Loop and add 7 to the start date until the end date is in the next month and day is less than 7 , to account for a Wednesday as last day of month like this month.
Again adjust the end date for this month, and then the start date for the next month.

Take it all in small steps, one at a time.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top