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.
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.