• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 55
  • Last Modified:

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)
0
Rajesh Pal
Asked:
Rajesh Pal
  • 8
  • 4
1 Solution
 
Rob HensonFinance AnalystCommented:
Exclude them from what?

Maybe a sample file would be helpful.
0
 
Shaun VermaakTechnical Specialist/DeveloperCommented:
0
 
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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??
0
 
Rajesh PalMISAuthor Commented:
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
0
 
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
1
 
Rajesh PalMISAuthor Commented:
Right

Thank you so much for help

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

like

Option 3   :        72:11
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Rob HensonFinance AnalystCommented:
Hold fire on using that, just checking some of the examples and they are not working out correct.
0
 
Rob HensonFinance AnalystCommented:
New formula:

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

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
0
 
Rob HensonFinance AnalystCommented:
Gives result as expected.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now