Calculate 1st Monday of the month and no of days in a month

RohitDangi

New Member
Hi Everyone,

I have to find the no. of days in a current month i.e 28,29,30 or 31 days. So that in a Calendar i can provide the input 'starting date' which is always be the 1st day of the month and 'end date' which will be last date of the month. I have to find the Mondays of a month.
If anyone have a solution or a suggestion it will be great help. Thanks.
 

VJR

Well-Known Member
Hi Rohit,

Based on your other post since you are already making use of Excel you can use formulas and maintain the list of Mondays in an excel.

Cell A2 has the Input date. Use any date of that month as the Input date and the formula will calculate the Mondays of that month. Refer the formula shown in cell B2.
View attachment 1547968465437.png

Cell B2
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+10-WEEKDAY(EOMONTH(A2,-1)+1))

Cell C2
=B2+7

Cell D2
=C2+7

Cell E2
=D2+7

Cell F2 (Applicable for months that have 5 Mondays, else will show blank)
=IF(EOMONTH(E2+7,0)-EOMONTH(A2,0)=0,E2+7,"")


Another option, is the one as shown in this link.
Scroll to the section that says List All Mondays / Fridays In A Month Or Year With Formulas

Input a date in cell A1 and in the next column get all Mondays of all months. Once you have all Mondays use a logic based on the month number to run only for that month.
doc-list-all-mondays-2.png
 
You can write a code stage or enhance the Date & Time VBo to have something like this:

'pass Inputs as startDate As Date, endDate As Date, weekdayInt As Number
'Output needs to be a DataTable outColl

Code:
        outColl = New DataTable()
        outColl.Columns.Add(New DataColumn("Date", Type.GetType("System.String")))
        outColl.Columns.Add(New DataColumn("WeekDay", Type.GetType("System.String")))

        For start As Integer = 0 To endDate.Subtract(startDate).Days
            Dim nextDate As Date
            nextDate = startDate.AddDays(start)
            outColl.Rows.Add(nextDate.ToShortDateString(), nextDate.DayOfWeek)
        Next start

That'll give you a BP Collection of dates and weekday names.

For the days in month, again enhance the Date VBO to have this for days in month:
pass Output as mDays(Decimal) and input as d(Date)
Code:
mDays = DateTime.DaysInMonth(d.Year, d.Month)
 

RohitDangi

New Member
Hi Rohit,

Based on your other post since you are already making use of Excel you can use formulas and maintain the list of Mondays in an excel.

Cell A2 has the Input date. Use any date of that month as the Input date and the formula will calculate the Mondays of that month. Refer the formula shown in cell B2.
View attachment 3086

Cell B2
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+10-WEEKDAY(EOMONTH(A2,-1)+1))

Cell C2
=B2+7

Cell D2
=C2+7

Cell E2
=D2+7

Cell F2 (Applicable for months that have 5 Mondays, else will show blank)
=IF(EOMONTH(E2+7,0)-EOMONTH(A2,0)=0,E2+7,"")


Another option, is the one as shown in this link.
Scroll to the section that says List All Mondays / Fridays In A Month Or Year With Formulas

Input a date in cell A1 and in the next column get all Mondays of all months. Once you have all Mondays use a logic based on the month number to run only for that month.
doc-list-all-mondays-2.png
Thanks alot VJR, the solution you suggested it works.
 

windfondquist

New Member
Hi Rohit,

Based on your other post since you are already making use of Excel you can use formulas and maintain the list of Mondays in an excel.

Cell A2 has the Input date. Use any date of that month as the Input date and the formula will calculate the Mondays of that month. Refer the formula shown in cell B2.
View attachment 3086

Cell B2
=IF(WEEKDAY(EOMONTH(A2,-1)+1)=2,EOMONTH(A2,-1)+1,EOMONTH(A2,-1)+10-WEEKDAY(EOMONTH(A2,-1)+1))

Cell C2
=B2+7

Cell D2
=C2+7

Cell E2
=D2+7

Cell F2 (Applicable for months that have 5 Mondays, else will show blank)
=IF(EOMONTH(E2+7,0)-EOMONTH(A2,0)=0,E2+7,"")


Another option, is the one as shown in this link.
Scroll to the section that says List All Mondays / Fridays In A Month Or Year With Formulas

Input a date in cell A1 and in the next column get all Mondays of all months. Once you have all Mondays use a logic based on the month number to run only for that month.
doc-list-all-mondays-2.png
Hi I have a requirement to calculate the start and end of the working Month,

The criteria is that if the 1st of the Month is a Monday then use that Date otherwise use the last Monday of the previous month.

Example data Below

DECLARE @Dates TABLE(ShortMonthName VARCHAR(3), [MonthStart] DATETIME,[MonthEnd] DATETIME)

INSERT INTO @Dates
(
ShortMonthName,
MonthStart,
MonthEnd
)
VALUES

contagemdedias
('Jan','2018-12-31','2019-01-27'),
('Feb','2019-01-28','2019-02-24'),
('Mar','2019-02-25','2019-03-31'),
('Apr','2019-04-01','2019-04-28'),
('May','2019-04-29','2019-05-26'),
('Jun','2019-05-27','2019-06-30'),
('Jul','2019-07-01','2019-07-28'),
('Aug','2019-07-29','2019-08-25'),
('Sep','2019-08-26','2019-09-29'),
('Oct','2019-09-30','2019-10-27'),
('Nov','2019-10-28','2019-11-24'),
('Dec','2019-11-25','2019-12-29')


SELECT * FROM @dates

I want to be able to create a function that will correctly calculate the start and end of the month for any given date, any year.

So far I have come up with this quite crude solution . However its not working quite correctly.

DECLARE @Date DATETIME='2019-01-28'



SELECT @Date =
CASE WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Monday'THEN DATEADD(month, DATEDIFF(month, 0, @Date), 0)
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Tuesday'THEN DATEADD(DAY,-1,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Wednesday' THEN DATEADD(DAY,-2,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Thursday'THEN DATEADD(DAY,-3,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Friday'THEN DATEADD(DAY,-4,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Saturday'THEN DATEADD(DAY,-5,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
WHEN DATENAME(WEEKDAY, DATEADD(month, DATEDIFF(month, 0, @Date), 0)) ='Sunday'THEN DATEADD(DAY,-6,DATEADD(month, DATEDIFF(month, 0, @Date), 0))
END


SELECT @date
The above example should return '2019-01-28' as it's the start of February, However, It's returning for the start of January.

Any help would be much appreciated.
 

Attachments

  • 1676198614486.png
    1676198614486.png
    709 bytes · Views: 3
Top