Calculate Total Working Days of month

can excel calculate working days in a month
calculate working days in excel
excel list working days in a month
working days calculator
how to calculate working days in excel excluding weekends and holidays
number of weekends in a month excel
excel first day of month
working days per month

I have this table where I am specifying Holidays in week. I want to calculate total working days between 2 specific dates using these fields.

CREATE TABLE [tbl_Shift](
[OffDay1] [nvarchar](25) NOT NULL CONSTRAINT [DF_tbl_Shift_OffDay1]  DEFAULT (N'Sunday'),
[IsAlternateOffDay2] [bit] NULL,
[OffDay2] [nvarchar](25) NULL
)

INSERT INTO [tbl_Shift] VALUES ('Sunday', 'True', 'Saturday')

I have this query written but I am not able to get correct days. It should give 23 days as there are 2 holidays in each week and 31 days in total but I'm getting 26 days.

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/05/01'
SET @EndDate = '2018/05/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
   -(CASE WHEN IsAlternateOffday2 = 1 THEN 1 END) FROM HRM.tbl_Shift

this will give 23:

SELECT
    (DATEDIFF(dd, @StartDate, @EndDate+1)) -(DATEDIFF(wk, @StartDate, @EndDate))
    -ISNULL((CASE WHEN IsAlternateOffday2 = 1 THEN (DATEDIFF(wk, @StartDate, @EndDate)) END), 0) 
FROM HRM.tbl_Shift

Excel formula: Workdays per month, To calculate the number of working days in a year, you can use the NETWORKDAYS function. NETWORKDAYS automatically excludes weekends and holidays if  I have this table where I am specifying Holidays in week. I want to calculate total working days between 2 specific dates using these fields. CREATE TABLE [tbl_Shift]( [OffDay1] [nvarchar](25) NO

This subtracts 2 days for every weekend. Check your server config if Sunday is first or last day of the week. This can throw things off by a weekend.

SELECT DATEPART(WEEKDAY,'20180506') --Checks if Sunday is Day 1 or Day 7

DECLARE @start DATETIME = '20180501'
DECLARE @end DATETIME = '20180531'

SELECT DATEDIFF(DAY,@start,@end+1) - (DATEDIFF(WEEK,@start,@end+1)*2)

UPDATE:

Use COALESCE to replace NULLS with an alternative value.

DECLARE @offdate DATETIME = NULL

SELECT COALESCE(@offdate,GETDATE())

Business Days Calculator – Count Workdays, Business Days Calculator counts the number of days between two dates, with the How many business days or non-working days are there between two dates, both date and time included; Date Calculator – Add or subtract days, months,  To calculate workdays per month, use the EOMONTH function together with the NETWORKDAYS function. In the example shown, the formula in C4 is: = NETWORKDAYS ( B4 , EOMONTH ( B4 , 0 ), holidays )

Try This

DECLARE @StartDate DATE ='2018-05-01',
        @EndDate DATE ='2018-05-31'
;WITH CTE
AS
(
SELECT DATEADD(DD,Number-1,@StartDate) MOnthDates,
       DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) As DayNAmes,
       CASE WHEN DATENAME(DW,DATEADD(DD,Number-1,@StartDate)) IN ('Saturday','Sunday') THEN 0 ELSE 1 END WeekDays
FROM master.dbo.spt_values
WHERE [Type]='P'
AND Number Between 1 AND 10000
)
SELECT COUNT(WeekDays)  AS WeekDaysCount
FROM CTE
WHERE WeekDays<>0
AND MOnthDates Between @StartDate AND DATEADD(DAY,1,@EndDate)

Result

WeekDaysCount
-------------
23

Demo:http://rextester.com/TOLYT35075

Excel formula: Workdays per month, While working with Excel, we are able to count only the workdays in any month by The dates in column B are actually the first days of each month, formatted to​  Calculate the number of working days, work hours and holidays between two dates in the USA. Add/substract days/working days to a date. We use cookies in order to offer you the best user experience.

Calculate the Number of Working Days in Each Month Using Excel , this will give 23: SELECT (DATEDIFF(dd, @StartDate, @EndDate+1)) -(​DATEDIFF(wk, @StartDate, @EndDate)) -ISNULL((CASE WHEN  Let’s you want to get total days in a month for the current month and you want this formula to return it automatically every time month changes. Formula will be: = DAY ( EOMONTH( TODAY (),0))

Calculate Total Working Days of month, function countDays($year, $month, $ignore) { $count = 0; $counter = mktime(0, 0, 0, $month, 1, $year); while (date("n", $counter) == $month) { if  Working Days in 2020. Using the interactive calendar table below you can easily find the number of working (business) days and hours in the 2020 year. The cumulative numbers for consecutive months and quarters are shown on the right. The calculations assume "standard" full-time employee work week, i.e. Monday-Friday, which gives 40 hours a week.

Calculate number of working days in a month, Working hours per day multiplied by working days per week multiplied by 52 weeks in the year divided by 12 months in the year equals to the average number of  Working Days in 2019 With our interactive working time calendar you can easily find the number of working (business) days in 2019 along with the respective number of working hours. For every month and quarter, the number of business (working) days and the working time (in hours) is shown on the right.

Comments
  • "This query"? I don't see a query in your question. :)
  • Whoa, Sorry my bad. Let me edit. Thank you for pointing out.
  • @Larnu Please check now. hope this helps.
  • I have updated the value of IsAlternateOffday2 to NULL and now I am getting NULL as total Count.
  • NULL Should be Considered as False, right.? and the output should 27 or 26.?
  • Output should be 27 and 23 in case Saturday is offday. I have made the update and this works for me.
  • But what If OffDay2 is NULL ?
  • this doesn't calculate correctly. Try from '2018-03-30' to '2018-06-15'
  • @t-clausen.dk Thanks For ur correction,just i updated the code