Calculating no of weekends in between the two given date and time fields by considering the time difference and output the difference in minutes

excel calculate working hours between two dates excluding weekends
calculate turnaround time in excel excluding weekends
excel calculate working hours between two times
calculate hours between two dates and times in excel
how to calculate working days in excel excluding weekends and holidays
net hours meaning
calculate working hours between two dates online
calculate work hours between two dates

I have two fields startdate and enddate. I need to calculate how many weekends in between two date and time fields and show the result in minutes.

For example start date is 01/11/2019 00:00:00 and end date as 03/11/2019 11:00:00. Below code is returning the difference in minutes correctly as 2100 minutes but when I keep the dates as02/11/2019 08:00 and 03/11/2019 00:00 I am getting the result as 1440 but my expected result is 960 minutes.

I understand that's because I am adding 1440 in code so how to correct this?

public double CountOfWeekEnds(DateTime startDate, DateTime endDate)
    {          
        double weekEndCount = 0;
        if (startDate > endDate)
        {
            DateTime temp = startDate;
            startDate = endDate;
            endDate = temp;
        }
        TimeSpan diff = endDate - startDate;
        int days = diff.Days;
        for (var i = 0; i <= days; i++)
        {
            var testDate = startDate.AddDays(i);
            if (testDate.DayOfWeek == DayOfWeek.Saturday || testDate.DayOfWeek == DayOfWeek.Sunday)
            {

                    if (testDate.Date < endDate.Date)
                    {
                        weekEndCount += 1440; // 24h * 60 min
                    }
                    else
                    {                        
                    var todayStart = new DateTime(testDate.Year, testDate.Month, testDate.Day, 0, 0, 0);

                       var difference = (endDate - todayStart).TotalMinutes;
                        weekEndCount += difference;
                    }                  
            }
        }
        return weekEndCount;
    }

Trying to remain as much of the original code as possible, only three minor changes have to be made:


1. Use the actual dates to calculate diff:

TimeSpan diff = endDate.Date - startDate.Date; instead of TimeSpan diff = endDate - startDate;

This is because later in the upcoming for-loop you are trying to evaluate each date in order to say if is a saturday or sunday. Otherwise, you are evaluating if the date 24 (, 48, …) hours after your starting time stamp is a saturday or sunday.


2. Use testDate instead of todayStart in order to calculate difference
difference = (endDate - testDate).TotalMinutes;

instead of

var todayStart = new DateTime(testDate.Year, testDate.Month, testDate.Day, 0, 0, 0);
var difference = (endDate - todayStart).TotalMinutes;

This is because testDate does contain the hours and minutes to calculate the difference in minutes. Otherwise you are just ignoring the day time of the starting day. Note that this correction can lead to a negative difference value if the startDate day time is later than the endDate day time.


3. do not add a whole day if there is only one day to examine in total

That means that if startDate.Date == endDate.Date, you should just calculate the difference between the dates.

if (testDate.Date < endDate.Date && startDate.Date != endDate.Date)

This has to be done because of the code logic: a full day is added for every new day other than the final day and for the final day ~24hours are added or substracted to the final value depending on the day times of the startDate and endDate.


The complete corrected code:
public static double CountOfWeekEnds(DateTime startDate, DateTime endDate)
{          
    double weekEndCount = 0;
    if (startDate > endDate)
    {
        DateTime temp = startDate;
        startDate = endDate;
        endDate = temp;
    }

    TimeSpan diff = endDate.Date - startDate.Date; //instead of endDate - startDate
    int days = diff.Days; 

    for (var i = 0; i <= days; i++)
    {
        var testDate = startDate.AddDays(i);
        //Console.WriteLine(testDate);
        if (testDate.DayOfWeek == DayOfWeek.Saturday || testDate.DayOfWeek == DayOfWeek.Sunday) //only weekends count
        {

            if (testDate.Date < endDate.Date && startDate.Date != endDate.Date) { // added  startDate.Date != endDate.Date
                weekEndCount += 1440; // 24h * 60 min
                //Console.WriteLine("************************add 1440 ");
            }
            else
            {    
                double difference;
                difference = (endDate - testDate).TotalMinutes; //instead of endDate -  todayStart
                //Console.WriteLine("************************add " + difference);
                weekEndCount += difference;
            }                  
        }
    }
    //return days;
    return weekEndCount;
}

How to calculate net work hours between two dates excluding , Select two cells which you will input the start date time and end date time, and Now the net working hours between two dates excluding weekends are counted. to add a number of days, years, months, hours, minutes, or seconds to this date . moreover, you can caculate the date difference, or the age based on a given� Now the net working hours between two dates excluding weekends are counted. Tip: In the formula, A2 is the start date time, B2 is the end date time, 8:30 and 17:30 are the general start time and end time in each day, you can change them as you need. Calculate net working hours excluding weekend and holidays. 1.

OK, i simplified what i said a little down to:

        DateTime start = new DateTime(2019,11,1,0,0,0);
        DateTime end = new DateTime(2019, 11, 3, 11, 0, 0);

        TimeSpan diff = end - start;

        Console.WriteLine(diff.TotalDays);

        int total = 0;
        for (int i = 0; i<Math.Ceiling(diff.TotalDays); i++)
        {
            DateTime test = start.AddDays(i);
            Console.WriteLine(test.DayOfWeek);
            if (test.DayOfWeek == DayOfWeek.Saturday ||  test.DayOfWeek == DayOfWeek.Sunday)

            {
                if (test.Date==start.Date)
                {
                    Console.WriteLine("start");
                    total += (23 - start.Hour) * 60 + (60 - start.Minute);
                }
                else if (test.Date==end.Date)
                {
                    Console.WriteLine("end");
                    total += end.Hour * 60 + end.Minute;
                }
                else
                {

                    total += 24 * 60;
                }
            }
            Console.WriteLine(test + " total " + total);
        }
        Console.WriteLine("done");
        Console.WriteLine(total);

which counts all saturdays and sundays and allows for start and ends to be partials

(and can someone send a keyboard with actual keys this membrain lark is hampering typings)

Excel formula: Get work hours between dates and times, To calculate total work hours between two dates and times, you can use a back out any hours on the start date that occur between the start time and lower time, The NETWORKDAYS function handles the exclusion of weekends and Like all Excel times, you will need to format the output with a suitable number format. 0 Calculating no of weekends in between the two given date and time fields by considering the time difference and output the difference in minutes Nov 11 '19 0 Can't find ADO.net Entity Data Model template in VS2017 Nov 12 '19

You need to have a look at this condition:

if (testDate.Date < endDate.Date)

It means that "as long as the ticks of testDate is less than the ticks of endDate". This condition will be true for all conditions that makes your variable "days" positive.

I think you need to extend this, condition e.g. if ((endDate - todayStart).TotalMinutes > 1440 )

This way it will check whether it is AT LEAST 24 hours earlier. If it isn't it should go forth with your "else" condition and take the used fraction of the start day into consideration.

Calculate working days between two date/time field excluding , I need to calculate working days between two date/time field Returns the difference between a start and end Datetime based on a specific set of business hours. 2nd question, if i want to calculate minute as well; which should i So your code provide the output in 1.00 for the Calculating_Date__c value� To calculate the minutes between two times, multiply the time difference by 1440, which is the number of minutes in one day (24 hours * 60 minutes = 1440). =( End time - Start time ) * 1440 As demonstrated in the following screenshot, the formula can return both positive and negative values, the latter occur when the end time is less than the

Here is a (somewhat) simple solution. Please note that the code could (and probably should) be refactored if it was to be production code. But I tried to optimize it for understandability, since it was your first post...

public static int CalculateWeekendMinutes(DateTime start, DateTime end)
{
    int weekendMinutes = 0;

    // First and last day will be handled seperately in the end
    var firstFullDay = start.AddDays(1).Date;
    var lastFullDay = end.AddDays(-1).Date;

    TimeSpan limitedSpan = lastFullDay - firstFullDay;
    int spanLengthDays = (int)limitedSpan.TotalDays;

    var dateIterator = firstFullDay;

    // Looping over the limited span allows us to analyse all the full days
    while (dateIterator <= lastFullDay)
    {
        if (dateIterator.DayOfWeek == DayOfWeek.Saturday || dateIterator.DayOfWeek == DayOfWeek.Sunday)
        {
            weekendMinutes += (24 * 60);
        }   
        dateIterator = dateIterator.AddDays(1);
    }

    // Finally we can calculate the partial days and add that to our total
    weekendMinutes += CalculateMinutesOnFirstDay(start);
    weekendMinutes += CalculateMinutesOnLastDay(end);

    return weekendMinutes;
}

// Helps us calculate the minutes of the first day in the span
private static int CalculateMinutesOnFirstDay(DateTime date)
{
    if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday)
    {
        // We want to know how many minutes there are UNTIL the next midnight
        int minutes = (int)(date.Date.AddDays(1) - date).TotalMinutes;
        return minutes;
    }
    else
    {
        return 0;
    }
}

// Helps us calculate the minutes of the last day in the span
private static int CalculateMinutesOnLastDay(DateTime date)
{
    if (date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday)
    {
        // We want to know how many minutes there are SINCE the last midnight
        int minutes = (int)(date - date.Date).TotalMinutes;
        return minutes;
    }
    else
    {
        return 0;
    }
}

Solved: Time difference between two date/times excluding w , I am looking for help to add a column that calculates the # of hours or days (not Time difference between two date/times excluding weekends and holidays However, if you need to calculate work hours between two date time If this post helps, then please consider Accept it as the solution to help the� -1 Calculating no of weekends in between the two given date and time fields by considering the time difference and output the difference in minutes Nov 11 '19.

SAS : INTCK Function with Examples, 2. start-date-or-time : Starting date or time to calculate the number of periods. Since 01JAN2015 is a starting date, it is specified in the INTCK function before INTCK says there is a month difference between 25OCT2016 and 03NOV2016. Suppose you need to calculate hours, minutes and seconds between two� Supposing, you have two columns of date time cells, and now, you want to calculate the difference in days, hours and minutes between these two date time cells as following screenshot shown. This article, I will talk about some methods to solve this task for you. Calculate days hours and minutes between two dates with formula

Calculate Minutes Between Date & Time In Microsoft Excel, Calculate the exact minutes by subtracting two dates in excel 2016. Well, in excel 2016 calculating the time difference is quite easy. We subtract time/dates in excel to get the number of days. Need to combine date into one column so that other calculation formulas can handle them for subtraction from other dates� In general, the difference between two SAS dates in days can most easily be calculated as duration=end_date - start_date. If you want to consider people who (in this case) are admitted and discharged on the same date as having a duration of 1 day, then the formula becomes duration=end_date-start_date+1.

Tableau, Start Date Shift: if DATEPART('weekday',[Start Date])=6 then DATEADD('day',2 Duration: 24:29 Posted: Jan 13, 2019 With Kutools for Excel’s Date & Time Helper utility, you can easily add days, years, months, or hours, minutes or seconds to a date time, moreover, you can caculate the date difference, or the age based on a given birthday without remembering the formula at all. Click for full-featured free trial in 30 days!

Comments
  • Hey, just to be clear. You want to calculate the number of weekend minutes, within your timespan?
  • YES, That's correct. Thank you Noceo
  • Thank you - @Liam
  • well, if not first or last, assume 24hours, if not sat or sun add 0 else add max mins, then sort out first and last day depending on times specified
  • @BugFinder - Could you please show that in code. Thanks
  • Hi BugFinder, While I am testing the above code, with the following examples I am getting wrong calculations. Please can you have a look Start date as 02/11/2019 00:00 and 02/11/2019 16:00
  • OK, Thank you, I thought there is some bug in code that's why I came back to you. I will work it out to fix the issue.
  • I never said it handled same day :P however, it shouldnt be hard to fix, at the same time it will help you understand it better if you work out why rather than i just fix it for you
  • Hi Noceo, Above code is giving wrong answer if I take 31/10/2019 00:00 and 04/11/2019 12:00 as example. Please can you have a look. Thanks
  • My bad, I forgot some stuff while moving code around. It should work now. Though for a production implementation, I would use the solution of @BugFinder, albeit with a bit more comments :-)
  • OK, Thank you - Noceo