Get number of seconds between two dates in excel excluding part of weekend & holidays

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

You need to calculate hours for whole ON days + part day from midnight to end time + from start time to midnight, while excluding the holidays.

Here is a workbook i created, that by chance (because the weekend time crosses past the beginning of the holiday #2 start time which itself ends before the end of day that has work hours at the end of the day) happens to give the correct value.

However, i will point out that this is designed for public/national holidays being a full day, and not for part day holidays (for example, that one might use for part day annual leave).

Here is the main formula (that uses many named ranges, NETWORKDAYS.INTL, MAX, MIN, MOD, etc):

Please Login or Register  to view this content.

working solution is in column D of the SLA sheet/tab.

sample file attached

Thank you M.janmorris for the solution i appreciate it
I one more quastion where we can specify time in the holiday list
i check it the time is 00:00:00
I read that If your business has different working hours for the weekend, then put this on separate rows. on read me but couldn't understand it
I am trying to add attachment here but it cant be loaded

In what way?

Scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

If your workbook is too big, then cut it down. We don't need to see the whole thing - just a sample that demonstrates the issue.

If your business has different working hours for the weekend, then put this on separate rows

in the example file there are 3 rows, where you can set the working hours and the days for those working hours.

so in your case you have some days are 24 hours, but other days are some other timeframe.

so on ONE ROW you can do ALL the days that have 24 hours
then for day where the time ENDS in the middle of the day, you switch the correct column ON (using "0"), and then go from midnight to the end time.
then for the day where the time STARTS in the middle of the day, but ends at midnight, you switch the correct column ON (using "0"), and then go from start time to midnight.
(i have already set these 3 rows up in the example file)

you can add in more rows to the setup configuration sheet by adding in a repetition of a part of the formula, and then adding named ranges where necessary, then modifying it slightly, like this type of pattern:

Please Login or Register  to view this content.

but for your example, the 3 rows is what is needed.

as for the hours start and end of specific day holidays, the system is not yet setup for that.

i suspect that to include this feature may require some kind of helpers to first create an array of "runs of days" between the original start and end date, and then taking weekends out from each row of the new array.

what you mean

...

lets use plain numbers as an example:

start with 1 - 100.

first, taking out the one-off breaks (holidays) from 25.5 - 26.6, and from 51.1 - 51.9

so now there are three runs of numbers:
1 - 25.5
26.6 - 51.1
51.9 - 100

now we can take out the regular numbers that occur every week.

does that make sense?

where we can specify time in the holiday list

to answer this previous question... i already stated that the Holidays list is for whole day holidays, it is to be used as deduction in NETWORKDAYS.INTL function.
it is not designed for using time. see above where i have explained what is really required, and can easily be implemented on the spreadsheet provided. All that you will need is a way to get the "run of datetimes" which you can implement by manually looking at the start datetime and end datetime and then making breaks in this run so you have a list of shorter datetime runs, then sum them together after calculation...

OR... figure out a way for these (shorter datetime runs) to be calculated and returned in an array/list that can then be put into the SLA sheet/tab.

i have attached a revised sample that has 5 rows on the SLA tab, which is enough for 4 of your "holidays" to be accounted for... if you want more holidays.. then add more rows.

As previously stated, the Full Day Holidays are for FULL DAYS ONLY.
This is because NETWORKDAYS.INTL considers a holiday to be a whole day from midnight to midnight.
it can NOT be used for a timeframe from datetime1 to datetime2

the way to implement this is to use the rows of the SLA sheet to be each remaining "uptime" after the holiday hours are removed. from the original time period.

@Nas Ro,

Here is a spreadsheet that will remove the downtimes (holidays) from the original time period, leaving you with just the remaining uptime (available time), which you can then use the values to fill the times in the SLA sheet of file provided in post #8.

In order to get the remaining uptime, first, the original downtime list is sorted, using this formula:

Please Login or Register  to view this content.

then, named ranges are used for the original start time and end times, and also for the range of the downtime start times, and the downtime end times.

Where the results will be populated starting on row 25, use this formula to populate the remaining start times:

Please Login or Register  to view this content.

and this formula for the remaining end times:

Please Login or Register  to view this content.

if you change the starting row where your results will be populated, then just change the number 25 throughout the formulas.