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

**helpme10** · 10-09-2012, 03:26 AM

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

**Sindhus** · 10-09-2012, 03:34 AM

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.

**helpme10** · 10-09-2012, 04:07 AM

Hi Sindhus, thank you for your reply!

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)

**Sindhus** · 10-09-2012, 04:47 AM

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

**daddylonglegs** · 10-09-2012, 04:58 AM

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

**helpme10** · 10-09-2012, 07:05 AM

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

**joaquinq** · 02-04-2018, 01:45 AM

Nice Formula Daddy.

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

Thank you

**FDibbins** · 02-04-2018, 02:53 AM

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.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new 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.

**Bryeguy** · 07-29-2018, 09:28 AM

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

**Bryeguy** · 07-29-2018, 09:29 AM

Originally Posted by daddylonglegs

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!

**AliGW** · 07-29-2018, 11:02 AM

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

**batangyagit** · 03-30-2019, 02:36 PM

Hi daddylonglegs,

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.

Thanks in advance!

**AliGW** · 03-30-2019, 05:07 PM

Administrative Note:

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions