Blog Post by Murat Yaşar


Posted on Monday, March 07, 2022 2:58:58 PM and it has been read 1580 times since then.


Calculate Time Difference Between Two Dates Excluding Weekend and Special Holidays

Hello, it's been quite some time since I last wrote on my blog.

This one also will be a short one, not long.

I needed a method that calculates the time difference between two dates. Also, I needed to exclude weekends and special holidays from the calculation.

Maybe it will help someone, at least as a starting point.

As you can see the method takes a parameter named holidays. This parameter is the list of special holidays.

Data is read from database and sent to this method as dto object. Below I will show both the table and the dto object scripts.

CREATE TABLE [dbo].[SpecialHolidays](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[SpecialHoliday] [datetime2](7) NOT NULL,
	[AllDay] [bit] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[DateCreated] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_SpecialHolidays] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SpecialHolidays] ADD  CONSTRAINT [DF_SpecialHolidays_AllDay]  DEFAULT ((1)) FOR [AllDay]
GO

ALTER TABLE [dbo].[SpecialHolidays] ADD  CONSTRAINT [DF_SpecialHolidays_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO

ALTER TABLE [dbo].[SpecialHolidays] ADD  CONSTRAINT [DF_SpecialHolidays_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'We write 1 if it is a full day''s holiday and 0 if it is a half-day holiday.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SpecialHolidays', @level2type=N'COLUMN',@level2name=N'AllDay'
GO
public class SpecialHolidaysDto
{
    public DateTime SpecialHoliday { get; set; }
    public bool? AllDay { get; set; }
}

You can see a sample code block below.

private static double CalculateInHours(DateTime startDate, DateTime endDate, 
                                       List<SpecialHolidaysDto> holidays)
{
	TimeSpan interval = new TimeSpan();

	DateTime savedDateForHoliday = new DateTime(startDate.Year, startDate.Month, 
                                                startDate.Day, startDate.Hour, 
                                                startDate.Minute, startDate.Second);

	bool firstIteration = true;
	
	for (DateTime dateValue=startDate;dateValue<=endDate;dateValue=dateValue.AddHours(1))
	{
		if (dateValue.DayOfWeek==DayOfWeek.Saturday || dateValue.DayOfWeek==DayOfWeek.Sunday)
		{
			dateValue = Convert.ToDateTime((dateValue.AddDays(1)).ToString("yyyy-MM-dd"));

			continue;
		}

		if (firstIteration == true || savedDateForHoliday.Date != dateValue.Date)
		{
			savedDateForHoliday = dateValue;

			firstIteration = false;

			var specialholidayvalue = holidays
                                      .Select(x => new { 
                                                        date = x.SpecialHoliday, 
                                                        isalldaylong = x.AllDay })
                                      .Where(y => y.date == dateValue.Date).FirstOrDefault();

			if (specialholidayvalue != null)

				savedDateForHoliday = specialholidayvalue.date;


			if (specialholidayvalue!=null && dateValue.Date==specialholidayvalue.date.Date)
			{
				if (specialholidayvalue.isalldaylong == false)
				{
					interval += TimeSpan.FromHours(4.5);
				}

				dateValue = Convert.ToDateTime((dateValue.AddDays(1)).ToString("yyyy-MM-dd"));

				continue;
			}
		}

		interval += TimeSpan.FromHours(1);
	}
	
	return interval.TotalHours;
}

Have a great day.


(In order to use this feature, you have to register.)

Tag Related Blog Entries

Creating Connection and Making CRUD Operations From Your .NET Project to Oracle DB

Thursday, December 29, 2022 0   761  

Scratching Beneath the Surface

Friday, March 11, 2022 0   1716  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   7817  

File Is Being Used By Another Process

Monday, August 29, 2011 1   3857  

Converting String From Turkish To English

Sunday, December 19, 2010 0   2228  

LINQ

Friday, October 08, 2010 0   3297  

Debugging in .NET

Saturday, February 13, 2010 0   1968  

VS 2010 / .NET 4 Release Candidate

Tuesday, February 09, 2010 0   2229