Avatar of Rajesh Pal
Rajesh Pal
 asked on

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)
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Rob Henson

Exclude them from what?

Maybe a sample file would be helpful.
Shaun Vermaak

Rajesh Pal

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

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 Pal


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 Henson

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
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rajesh Pal


Thank you so much for help

Can u share the formula for same.
Rob Henson

If you answer the question on how you want it shown then I can put together the correct formula.
Rajesh Pal

I need answer only in Hours format


Option 3   :        72:11
Your help has saved me hundreds of hours of internet surfing.
Rob Henson

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 Henson

Hold fire on using that, just checking some of the examples and they are not working out correct.
Rob Henson

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

Gives result as expected.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.