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

Do you mean business hours between two dates?

https://www.extendoffice.com/documents/excel/3825-excel-calculate-net-work-hours-between-two-dates-excluding-weekends.html

https://www.extendoffice.com/documents/excel/3825-excel-calculate-net-work-hours-between-two-dates-excluding-weekends.html

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

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

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

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

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

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

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

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

Right

Thank you so much for help

Can u share the formula for same.

Thank you so much for help

Can u share the formula for same.

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

I need answer only in Hours format

like

Option 3 : 72:11

like

Option 3 : 72:11

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

=(MAX(0,DATEDIF(A2,B2,"d")

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

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)

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

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

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

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.

Gives result as expected.

Maybe a sample file would be helpful.