Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Computing For Number Of Hours Under Night Differential

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More


How do i compute for the number of hours that fall into the Night Differential hours within an employees shift? In one cell is the shift start time of an employee and on the other cell is the shift end time. There is no constant number of hours that an employee should complete. Night Differential time starts at 10:00 PM and ends at 6:00AM.


View Answers     

Similar Excel Tutorials

Excel Time - Summing Time Greater than 24 Hours
I'll show you the easy and simple way to sum time past 24 hours in Excel.  All we really have to do is to change th ...
Calculate the Difference Between Time Greater than 24 Hours in Excel
Ill show you how to calculate the difference between two times in Excel when that difference will add up to more t ...
How to Make Macros Run A LOT Faster
Here is a very simple and easy-to-use tip to make all of your Excel macros run A LOT faster.  It is very simple and ...
Change Minutes from a Decimal to a Percentage of an Hour
Change minutes stored as a decimal into a fraction of an hour. For instance, if time is stored as 8.15, 8 hours and ...
Calculate the Difference Between Two Times in Excel
Here, youll learn how to get the difference between two times in Excel. A common example of this is for when someo ...
Calculate the Total Time Worked Minus Lunch Breaks in Excel
Ill show you how to use Excel to calculate the total time worked in a day minus lunch time or any breaks. This is ...

Helpful Excel Macros

Automatically Run a Macro so Many Seconds, Minutes, or Hours After an Excel Workbook has been Opened
- Run a macro after a certain amount of time has passed since the Excel workbook was opened. This means that you can set
Automatically Run a Macro at a Certain Time - i.e. Run a Macro at 4:30PM every day
- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Save the Current Worksheet as a New File in the Current Folder
- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from

Similar Topics

I need a formula to figure the number of hours that a person works in there 8 hour shift that falls under the category of Night Differential. An employee is paid an extra 10% per hour more if during their assigned shift is from 6:00p.m. (1800) until 6:00 a.m. (0600) the next day. For example if an employee is scheduled to work from 6:00 p.m. on Monday evening until 2:00 a.m. Tuesday the next morning the employee would have worked 8 hours of Night Differential as well as their regular 8 hour shift. How can I use a formula that will figure out the number of hours, of Night Differential, that an employee works if they work from 1445 until 2245 (answer should be 4.75 hours) and from 2300 to 0700 (the next morning. I also need the same thing to figure out the number of hours an employee works on Sunday if an employee starts their shift on Sunday at 1800 and ends their shift on Monday morning at 0200 (answer should be 6 hours).

Cell....................B1 is SUN and so on across worksheet
....................B2 is 4/13

A3 is Start Time....B3 is 1445

A4 is End Time......B3 is 2245

A5 is Night...........B5 is (number of hours)

Best regards,


Time Sheet - Differential hours?

I'm having trouble doing a time sheet with Differential hours. Ill Explain.

In a certain department, employees can work any time. When the employee works between the hours 7:00 AM to 7:00 PM, he/she gets paid "Regular hours". If the employee works between the hours 7:00 PM to 7:00 AM, he gets a bump in hourly pay for those hours.

Here is the situation. If I have an employee work the hours 4:00 PM to 10:00 PM I need to write 2 new sections in the time sheet for "Reg" hours and "Differential" hours. In this case, I need the end result Reg hours = 3:00:00 for 3 hours, and 5:00:00 for differential hours.

Here is the code I have for the collection of hours for a normal day w/ lunch deduction.

B9 - Time Start
B10 - Time End
B11 - Lunch Start
B12 - Lunch End


Can some one help? If you need me to elaborate more, feel free to contact me!

Kevin C.

Good Day,

I have two cells in my Excel file that contains the Start and Stop time, I was able to get the time difference between the 2 cells. Their cell format is dd/mm/yyyy 00:00 PM/AM (Custom).

I want to get the time differential (night shift) between the two cells but I can't figure it out. Night shift starts at 10:00 PM - 6:00 AM (next day), please see sample below.

C2: 08-July-2010 8:00 PM (Start)
D2: 09-July-2010 8:00 AM (Stop)

Would appreciate if you could help me build a formula to get the time accumolated for night differential.

Thank you.

Currently I have developed a Time sheet for employees however I am having a lot of trouble with the shift differentials right now.

First, I used an If function to say If(B7="E",B6,0) E would be the evening shift and of course this works fine if everyone worked an perfect Evening shift within the time limits however, they dont.

I'm wonderin if it is possible to have one, or multiple formulas that can do the following:

First shift differential is from 14:00-23:00
Second shift differential is from 22:00-07:00, However lets say you start at 14:00 I don't want it calculating the shift differential of the first one for 22:00-23:00.
Third shift differential is a weekend one which I have figured out it's rather simple to just have a IF function for that.

Any help would be appreciated. Thanks in advance.

Start time is B3
End time is B4
Breaks is B5 (However, Breaks is subtracted from B6)
Hours worked for the day is B6

I want to find the hours worked in day shift and night shift. Day shift is from 06:30 to 18:30 and night shift is from 18:30 to 06:30. Listed below is an example of my date/times.
Note that the night shift carries over to the next day.

21/12/09 07:00 to 21/12/09 11:09
21/12/09 07:46 to 21/12/09 14:41
21/12/09 12:13 to 21/12/09 22:08
21/12/09 16:40 to 21/12/09 18:05
21/12/09 19:40 to 22/12/09 02:34
21/12/09 23:20 to 22/12/09 04:39
22/12/09 02:06 to 22/12/09 06:15

any ideas?

Hi there,

I'm trying to make my own timesheet calculator for work. Now instead of doing it manually all the time and having to calculate the day and night hours separately, I'm trying to do something like, when I put in the start and finish hours, it automatically calculates the day and night hrs.

For example: day shift between 06:00 and 18:00, night shift 18:00 to 06:00.
I managed to calculate for day shift hrs, but could not figure out the night ones. Here is for the day ones: let's say A1 is the start time and B1 is the finish time. My formula looks like this: IF A1="","",(MIN(B1,TIME(18,0,0))-MAX(A1,TIME(6,0,0))+(B1


I am trying to do a table to count the hours i have worked, including how many are at night and day.

for example

22:00 - 06:00 is our night hours
06:00 - 22:00 is our day hours.

if i clocked on at 05:00 and clocked off at 10:00 i would of worked

1 hour at night
4 hours at day

can someone work a formula to help me with this please. i would like to enter my start time in one cell, finish time in another cell. then the values to populate "day hours" and "night hours"


I have been working on my own roster over time with some help already from this forum and am stuck on one thing. I can choose from a drop down list which employee I want to do a shift after this the name no longer shows for that day only. The following day the name returns which is great. Once the name is chosen it auto calculates man hours on second sheet.

My question is this, is there any way for the drop down list to not show the employees who were on the Night shift in the followings days Day Shift and vice versa?

I hope I have explained that properly I would appreciate any help. I have attached a copy of the roster to help.


Our employees work many different shifts. Whatever shift they work, they get night premium for those hours that fall between 6PM and 6AM the next morning. Some shifts start and end before midnight, and other shifts start before midnight and go until after midnight, and some start before midnight and go until after 6AM, and others start at midnight and go until after 6AM. Below is an attachment showing some example shifts. I need a formula that will calculate the number of hours in the shift that fall between 18:00 and 06:00. If possible, I need the same formula to be used whether or not the shift crosses midnight. Thanks in advance for any help.


I am encoutering a few issues with the following:

I would like to have a formula to tell me this is night shift or day shift.

Day shift starts at 6:59:59
Night Shift starts at - 19:00:01

So for example what formula would i use if I had
Mon04/01/2010 23:46

Thanks for you help.


I am trying to develop a way of solving a day shift/ night shift problem (I attach a sample data dump the actual data to be analysed is hundreds of lines). If I have an exited off-site as the first entry of the day this means that the person worked night shift and I need to calculate the hours on site for that day from midnight. If the first entry of the day is a entry to site then I need to calculate the time between this event and the exited off-site event for each employee. If the employee does not exit off-site then I need to calculate the time from the entry to site until midnight.

The sample data is in the format I can extract from my source system. Please assist me in solving this issue.


I'm pretty new at the whole Excel thing and many of the formulas are way over my head.
I'm trying to figure out how to make a time sheet calculate straight time and overtime and I can not figure it out. The time sheet from the Excel Bible was close, but not quite what I'm looking for.

I work seven rotating twelve hour shifts and anything over eight hours and anything over twelve hours is overtime. The shifts start at 0600 and 1800.

I have several questions.
1. how do I get the times to calculate? It works correctly when doing one shift (days), but not the other (night). It adds up to 24 on night shift.
2. how do I make it seperate the twelve hours into 8 straight time hours and four over time hours?
3. how do I get it to know when it has reached the 40 hour mark?


I'm trying to automate our payroll to calculate the number of hours worked to factor in shift times & penalty rates. At present, I have worked out how to calculate the number of hours worked with penalties but I don't know how get excel to recognise shift times.

I'm using the following formulas to work out;
1. Total hours worked (less unpaid breaks): =(D4-C4+(D4<C4))*24-0.9
2. Standard hours: =IF(E4>=7.6,7.6,E4). This is for a normal full working day (7.6hrs)
3. Time & a half penalty rate (first 2 hours): =IF(E4>=9.6,2,E4-F4)
4. Double time penalty rate (each hour thereafter the first 2 hours): =IF(E4>=15,0,E4-F4-L4)

These formulas work well but it won't work when I try & add shift time parameters. The shift times are worked out on the following time periods:
1. Morning shift: Shifts that finish between 12:30 & 14:30
2. Early afternoon shift: Shifts that finish between 19:00 & 21:00
3. Afternoon shift: Shifts that finish between 21:00 & 23:00
4. Night shift: Shifts that finish between 23:00 & 07:00

These shifts attract penalty rates also so I need a way to allocate the time to the correct cell. The actual $$ value calculations are done elswhere on the sheet.

I've tried a number of different ways to overcome it but I don't have a lot of experience with excel & only know how to use basic formulas. Any assistance would be greatly appreciated>


I need to create a spreadsheet that will allow me to calculate what my average daily temperature would be in a greenhouse environment. Basically, there would be four columns: Night Temperature, DIF temperature(a reduced temperature temperature to stop plant stretching), Day Temperature and ADT (Average Daily Temperature). Each cell would be a given percentage of a 24 hour day. Say, for instance, that my day time temperature would last for 8 hours, then that temperature would be approximately 33% of my average daily temperature.

I would like to be able to fill in the cells for my Day, Night and DIF temperatures and have an ADT automatically calculated. Also be able to change the duration of the day, night or dif temperature periods....or maybe have multiple tables for different scenarios, e.g.:

Day - 10 hours
Night - 10 hours
DIF - 4 hours


Day - 11 Hours
Night - 10 Hours
DIF - 3 hours

There could be many different scenarios of how long each temperature could last. My question is what type of formulas would allow me to accomplish this?

Thank You

hi im a beginner in excel and i need an overtime sheet which calculates the hours of

employees with standard and over time rate. But the hardest part for me is that

there are so many conditions which is confusing me.

1)Week start from Sunday to Saturday
2)standard hours are 40/week
3) if it goes above 40 they are entitled for an overtime
4)now there are different rates of hours.
5)std day rate, std night rate, std day overtime rate, std night overtime rate,

weekend day rate, weekend night rate, weekend day o/t rate and weekend o/t night


The hours can start from any day and finish by saturday.
would like to calculate if
1) they work on days, they get paid for days.
2) if they work for nights, then get paid for nights.
3) if the work in the middle they should get paid both night and day mixture
4)as soon the hours hit above 40 , the o/t starts.
5)only those hours need to be calculated for overtime which are after 40 hours. Now

the hard part is , they can be days, nights , or weekends.
I am so confused as I dont know how to get it right.

Any help will be appreciated.

I'm struggling to find the appropriate formula to calculate certain time increments for various work-shifts. I have a start time,finish time and increments of time across the spectrum of 24 hours. There are also multiple start time across the 24 hour period with some start times begining on one day and ending on the next day.


In B5 Startime is 22:00
In C5 Finishtime is 06:30

In I3 increment begins at 00:00
In I4 increment ends at 00:30

The employee working the shift from 22:00 - 06:30 would fall into the time increment of 00:00 - 00:30 where another employee working a different shift (08:30 - 17:00) would not. I'm looking for a formula that would return a 1 in a cell if the employee fell into the 00:00 - 00:30 time increment and a 0 in a cell in the employee did not fall into the time increment.

Each time I feel I'm close I realize I'm not!!!


I am trying to account for all hours punched on a time clock in 1 hour time slots. My approach is to use this formula =MAX(MIN(DF52,$I52)-MAX(DE52,$H52),0). DF = 0 (or 12:00AM), I = Employee Out Punch - DE = 23:00 (or 11:00 PM), H = Employee In Punch, to determine if any portion of the shift falls between 11pm and midnight. In this example any hours worked in a shift between 11:00pm and 12:00am should show up in the designated column. In general this formula works great with 2 exceptions. (1) Any increments of time that are less than 1 hour do not show up in the designated time slot bucket (example, 8am - 9:45am the 45 minutes does not populate in the 9am to 10am bucket, the rest of the shift appears accurately). (2) The above formula does not for any shift ending at midnight, 0 (or 12:00AM).

Any assistance is appreciated.

I am setting up an estimating spreadhseet for a construction company. Daily, they pay 8 hours of straight time and everything after 8 is over time, except Sundays & Holidays are paid at double time.

I wanted to be able to input the number of personnel by craft x the number of days & hours per shift for a day and night shift to get a man hour total split up by straight time, over time and double time. Mostly, the estimator will fill in the number of craft per day or nigh shift, and the red items.

I think I have the straight time man hour claculation/formula figured out and probably the over time. My problem seems to be the double time formula and getting it to deduct the straight time and overtime from the total. (May be that I am making this harder than needed?)

So my issue is with the formula in cell C91 and something has to happen to the formulas in C89 and C90.


I am trying to create a formula to calculate hours worked (clock in/clock out). The shift that worked is from 5pm-1am or whenever the work is done.

I can calculate hours for a shift worked in the same day (8am-5pm) but not one that is worked from the night before to the next morning. Can someone help me?



I am no where near advanced enough in my excel to kknow where to start this so I am hoping someone can aid me in this. Let me explain what I am looking for.

I am in charge of scheduling three different departments with various sizes, I am currently using three different excel workbooks and would like to keep it that way.

I am trying to set up a way to auto schedule the employees using a macro or formula, by taking the information from various locations and then auto generating a roster from that information. This would have to be based on the following for each department.

I have all the schedules created and broken down into the various classes but I am in need of a way to simplify the processing of this roster as it takes me 30+hours a week to schedule and organise the various areas. Any help is greatly appreciated.

Department 1.
15 employees
Work 20 days, off 8 days
10 hours per day
6-8 emplyees needed per day
various shifts (4am to 2pm, 7am to 5pm, 10am to 8pm, 2:00pm to 12:00am and 9:00pm to 7:00am)
Employees leaving on the 20th day would have to be scheduled an early am shift.

The roster would have to ensure propper coverage per day and rotation.

Department 2 & 3

150 employees each department
Work 21 days, off 7 days
10 hours per day
65 - 80 emplyees needed per day

Department 2
60 employees into one classification (Day Shift)
This then broken down into
50 (Cleaners)
10 (Janitors)
10 employees into another classification (Night Shift)
This broken down into
8 (Cleaners)
2 (Janitors)

The roster would have to ensure propper coverage per day and rotation.

Department 3
35 employees into one classification (Day Shift)
This then broken down into
General Help
Sandwich maker
Salad maker etc

35 employees into another classification (Night Shift)
This then broken down into
General Help
Sandwich maker
Salad maker etc

The roster would have to ensure propper coverage per day and rotation.

Hello all, thank you for your curiosity and help. I am a beginner at excel and need help creating a formula at work. Here is the what im up against:

Column B (staffed Time), Format 8 hours = 8:00:00AM=formated 8:00:00

Column E (break time), formated(15 or 30 minutes depending on shift)=12:30:00am= 0:30:00

Column M (expected time) (time on phones if perfect +feeding unused breaktime back into expected time (eg 8 hours shift - 30 break = 7.5hours = 7:30:00am = 7:30:00 or 8:00:00-0:30:00. ( if only used 10 minutes of break of 30 total put 20 minutes back into expected time so that 8:00-0:30:00+0:20:00 = 7:50:00. & also if possible add in any overages from staffed time, If they log in 5 minutes early make that staffed time 7:35:00 assuming 30minutes of break, but no underages if they are late tough. Tough huh? I was using cells to reference different employees shifts eg 8 hours & 30 minute breaks or 4 hours & 15 minute breaks. I have different books for each employee & column a is just dates of each day of the month. Help me if you can. Ask me questions. Would like to know by tomorrow if possible.

Just running windows xp & excel 2003 sp3. I came up with a partial formula =IF(E3>=$M$40, $M$41, ($M$41)+($M$40-E3)) but it didnt work with the staffed time overages & also filled in days that were blank as if they worked 8 hours. I just deleted them but if we can get it to not fill them great.

I have two time zones 07:00 to 00:00 is day rate and 00:00 to 07:00 is night rate.

Electricity is charged at 10.3456 day rate and 6.3333 night rate.

My lights come on in cell f3 and off in cell g3 so:-

f3=20:45 and g3=22:00 this equal 1.25 hours on time based on =IF(G3>F3,G3-F3,24-F3+G3) also

f3=07:00 and g3=01:00 which is 18 hours on time based on the formula above.

f3=20:30 and g3=02:30 which is 6 hours on.

I also have lights running 24/7

How do I determine what portion of the hours are day rate and night rate to be able to calculate the cost of running the lights.

Thanks in advance.

Hi all,

I'm trying to create a timesheet that calculates hours worked for 3rd shift (overnight) employees.

I can't get it to return anything but negative values. I'm using the following formula:

=SUM((G10-E10),(D10-B10))) -- this takes into account both shift start and end time as wel as Lunch.

It works fine when using it for hours during the same 24 hour period, but not when the hours cross midnight.

Anyone have any ideas??



I have two 12 hour shifts and there are two pay scales. The pressmen receive a night shift premium from 7pm-6am. Their shifts are 6pm to 6am, and the other shift is 6am - 6pm. How do I seperate the hours worked between regular and premium without manually doing it? I want to put in the hours worked and formulas calculate it out for me (if possible).
Thank you

Hi there.

I am working on a schedule to work with an hourly budget. I was wondering if there is a way to type in hours for an employee to work and have it totaled at the bottom of the column for what day is worked.

Schedule Date 10 11 12 13 14 15 16 Sun Mon Tues Wed Thurs Fri Sat Employee 12-8 Employee 9-5 11-730 Employee Employee 8-4 2-1030 Employee 12-8 Total hours: 8 8 16 8.5 8.5

This is an example of what I mean. The problem I run into is I am unable to tally up all the hours or create a formula for excel to understand that 12-8 is 8 hours. I would really prefer to not have to type in the actual time either. (8:00AM-4:00PM, 12:00PM-8:00PM)

Any help is greatly appreciated!