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

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
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

No

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
Rajesh Pal

Right

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

like

Option 3   :        72:11
Rob Henson

Here is the formula:

=(MAX(0,DATEDIF(A2,B2,"d")-1))*10/24+IF(MOD(A2,1)>TIME(19,0,0),0,TIME(19,0,0)-MOD(A2,1))+IF(MOD(B2,1)<TIME(9,0,0),0,MOD(B2,1)-TIME(9,0,0))

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:

Days:
=MAX(0,DATEDIF(A2,B2,"d")-1))*10/24

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:
=IF(MOD(A2,1)>TIME(19,0,0),0,TIME(19,0,0)-MOD(A2,1))

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:
=IF(MOD(B2,1)<TIME(9,0,0),0,MOD(B2,1)-TIME(9,0,0))

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.

Thanks
Rob
Time-calculation.xlsx
Rob Henson

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