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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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?
 
Upvote 0
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
Code:
weekday(dateserial(Year(date),month(date),0)+1)

Weekday of last of month
Code:
weekday(dateserial(Year(date),month(date)+1,0)

then do the math for your Thursday or Wednesday.

HTH
 
Upvote 0
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
Code:
weekday(dateserial(Year(date),month(date),0)+1)

Weekday of last of month
Code:
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.
 
Upvote 0
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.?
 
Upvote 0
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))"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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