Remove night hours for All week days

I need to exclude Night hours (19:00:00 to 09:00:00) for all week days like (Sat & Sun should be included)
Rajesh PalMISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Exclude them from what?

Maybe a sample file would be helpful.
Shaun VermaakTechnical Specialist IVCommented:
Rajesh PalMISAuthor Commented:
No I need to exclude only night hours weekend should be included.

below some date.

End date - Create date (exclude only night hours between 19:00:00 to 09:00:00 and weekend should be included)

Create Time                        End Time
25-10-2017 09:45                        01-11-2017 11:56
06-10-2017 13:01                        23-10-2017 17:33
22-10-2017 09:52                        22-10-2017 19:30
30-10-2017 13:16                        30-10-2017 13:52
29-10-2017 08:30                        30-10-2017 09:37
25-10-2017 18:51                        28-10-2017 14:37
27-10-2017 17:17                        27-10-2017 21:46
27-10-2017 16:40                        27-10-2017 21:46
23-10-2017 19:12                        25-10-2017 18:52
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
Can you give the expected result for those samples and the reason for the result.

For example, I think the result for the first one would be 6 days, 12 hours, 11 minutes. How do you want that shown?

6 days = 26 - 31 Oct, excluded 25 Oct and 1 Nov as not whole days.
12 Hours 11 minutes = 9:15 (9:45 to 19:00 on 25 Oct) plus 2:56 (9:00 to 11:56 on 1 Nov).

Is that correct??
Rajesh PalMISAuthor Commented:

i want to exclude time between 19:00:00 to morning 9:00

and for below date calculation for example

25-10-2017 09:45                        01-11-2017 11:56

time start from 25-Oct morning 9:45 to till 1st Nov morning 11:56 \

between above calculation Non-working hours after 19:00:00 to before morning  9:00:00 should be exclude.

i need excel formula for that calculation
Rob HensonFinance AnalystCommented:
Yes, I think I understand the requirement. Is the suggested result for the first example correct?

if so, how do you want it displayed?
Option 1: 6 days, 12 hours, 11 minutes
Option 2: 72 hours, 11 minutes  (6 days at 10 hours per day plus 12 hours)
Option 3: 72:11
Rajesh PalMISAuthor Commented:

Thank you so much for help

Can u share the formula for same.
Rob HensonFinance AnalystCommented:
If you answer the question on how you want it shown then I can put together the correct formula.
Rajesh PalMISAuthor Commented:
I need answer only in Hours format


Option 3   :        72:11
Rob HensonFinance AnalystCommented:
Here is the formula:


Cell is then Custom formatted as [hh]:mm

Enclosing the hh in [ ] will ensure that hours over 24 are not converted to days and will still be shown in the result.  

Attached is a file with that broken out into sections to show how it is calculated and detailed below:


Using DATEDIF function to get number of days between the two dates. DATEDIF counts both start and finish date so deducted 1 to ensure it only counts whole days, uses MAX to compare with 0 to ensure doesn't go negative after deducting 1.
Multiplied by 10 (09:00 to 19:00) to get number of working hours for those days and then divided by 24 to convert to a time value.

Start Day hours:

MOD function will take the non-integer part of a value, in your case just the time from a date & time value.
The IF statement checks that the Start time is not after 19:00, if it is it returns 0 else it works out time between start time and 19:00

Finish Day hours:

Similar to Start time but checks not before 09:00 and then works out time between 09:00 and finish time.

Total hours:
Start day hours + Finish Day hours

Total time:
Whole Days converted to hours plus Total hours

You will only need the formula in column L, the others are there just to show the breakdown.

Rob HensonFinance AnalystCommented:
Hold fire on using that, just checking some of the examples and they are not working out correct.
Rob HensonFinance AnalystCommented:
New formula:


Open in new window

I have broken into multiple lines to aid understanding, the formula should be copied as one and pasted into one cell.

For the explanation, I will explain by lines of the code snippet:

  1. INT function takes the integer value of a number, for Date & Time values this will be just the date; as mentioned earlier the MOD function takes the remaining non-integer or decimal value. If the comparison of Date Values is TRUE (dates are the same) then the formula uses line 2 otherwise it uses lines 3 to 5.
  2. The MIN function compares the time stamp in B2 (finish time) with a time value of 19:00 and takes the lower of the two. The MAX function compares the time stamp of A2 (start time) with a time value of 09:00 and takes the higher of the two. The calculated start time is then deducted from the calculated finish time to calculate time passed between the two.
  3. As per earlier description uses DATEDIF to work out number of whole days and converts to working hours
  4. Compares start time from A2 with a time value of 19:00 and if is later than 19:00 then gives result of zero otherwise it works out the time passed between the higher of 09:00 or the actual start time and 19:00
  5. Compares finish time from B2 with a time value of 9:00 and if is earlier than 9:00 then gives result of zero otherwise it works out the time passed between the lower of 19:00 or the actual finish time and 09:00

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Gives result as expected.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.