# How to count hours for Night Differential (using IF function)

1. ## How to count hours for Night Differential (using IF function)

I've tried to search for a solution to this problem but I haven't found one. Please help!!

LOGIC PROBLEM: If an employee's working hours falls between 10:00 pm to 6:00 aM, count no. of hours spent between 10:00 pm to 6:00 am

example

EMPLOYEE | TIME IN | TIME OUT | NIGHT DIFFERENTIAL
Jones, Harry | 10:00 am -10:00 pm
Smith, Paul | 11:00 am - 11:00 pm = 1 ND
Stone, Mark | 2:00 pm - 2:00 am = 4 ND
Philips, Jay | 8:00 pm - 8:00 am= 8 ND (anything over 6 am will not be counted)
Grey, Jean | 9:00 am - 9:00 pm
Ramos, Mary | 10:00 pm - 6:00 am = 8 ND

Thank you so much in advance!!! This will help a lot!

here's all the data above in excel format:
night differential problem.xls

2. ## Re: How to count hours for Night Differential (using IF function)

Hi, If in-time is A column, Out-time in B column, you can use this formula to find the time difference
Formula:
`Please Login or Register  to view this content.`

3. ## Re: How to count hours for Night Differential (using IF function)

i used your formula, however it yielded a .5 ND for almost everyone. im not just looking for the time difference for their in and out, but im looking for the no. of hours they spent between 10pm to 6am (if any)

basically, the correct results i need are under the Night Differential column (but i had to count the hours spent manually, i want excel to do it for me)

4. ## Re: How to count hours for Night Differential (using IF function)

Got it! Please check the attached file. I have used a supporting column E for getting the result.

5. ## Re: How to count hours for Night Differential (using IF function)

I don't know if it's a possible shift but shouldn't 05:00 to 13:00, for example, give a result of 1?

Try this formula in D2 to account for all possibilities

=MOD(C2-B2,1)*24-(C2<B2)*(22-6)-MEDIAN(C2*24,6,22)+MEDIAN(B2*24,6,22)

format as number and copy down

6. ## Re: How to count hours for Night Differential (using IF function)

hi daddylonglegs! OMG! that formula worked!!! this is fantastic! thanks so much!!!

i now have one more problem to solve in my payroll excel file regarding undertime..

Thanks so much for everyone who replied! :D

7. ## Re: How to count hours for Night Differential (using IF function)

One question, if they are tag as Day Off. What can we add to the formula?

Thank you

8. ## Re: How to count hours for Night Differential (using IF function)

joaquinq welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

9. ## Re: How to count hours for Night Differential (using IF function)

Can you explain what each of these values represent? I am trying to figure out how to apply your function into my spreadsheet.
Thanks!

10. ## Re: How to count hours for Night Differential (using IF function)

I don't know if it's a possible shift but shouldn't 05:00 to 13:00, for example, give a result of 1?

Try this formula in D2 to account for all possibilities

=MOD(C2-B2,1)*24-(C2<B2)*(22-6)-MEDIAN(C2*24,6,22)+MEDIAN(B2*24,6,22)

format as number and copy down
Can you explain what each of these values represent? I am trying to figure out how to apply your function into my spreadsheet.
Thanks!

11. ## Re: How to count hours for Night Differential (using IF function)

I suggest you start a new thread. Copy the formula there and ask for help. Suggested title: Help to Understand MOD & MEDIAN Functions.

12. ## Re: How to count hours for Night Differential (using IF function)

How do I do the same but would like to exclude lunch break if it falls within 10pm - 6am?
For example
for shift starts at 9pm - 6 am, and say lunch break is at 1am-2am, it will deduct 1 hour from the night differential mentioned above.
for shift starts at 5:30pm-2:30 am, and say lunch break is at 9:30-10:30, it will deduct 0.5 hours from the night differential.

13. ## Re: How to count hours for Night Differential (using IF function)

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

There are currently 1 users browsing this thread. (0 members and 1 guests)