E-mail:

# Calculating The Number Of Hours And Minutes Between 2 Times

I'm a little stuck calculating the number of hours and minutes between two time references. The formula I'm using works fine except for one exception. One of the shifts our staff works is 4:00pm to 12:00am.

I'm using the formula:

=TEXT(C3-B3,"h:mm")

When B3=4:00 pm and C3=12:00am, I'm getting an error. I'd like it to output 8:00 (representing the 8 hours between 4pm and 12am. Any ideas ?

## Similar Excel Tutorials

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

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
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
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Change Formulas to Absolute or Relative References
- This macro will change the formulas in cells to absolute or relative. It allows you to change the rows or the columns o

## Similar Topics

Okay, I've been looking everywhere to figure this out. I have a schedule all put together that currently calculates the total hours per person for the day and for the week. I now need to calculate the total number of hours in each shift separately. Shift 1 is before 4:00pm, and Shift 2 is after 4:00pm. No need to include lunch breaks since they don't have to clock off for breaks.

For example:

Evan worked from 6:00am to 4:00pm, so his hours should be included in Shift 1.

Sue worked from 4:00pm to 10:00pm, so her hours should be included in Shift 2.

And to make things even more complicated:

Scott worked from 11:00am to 8:00pm, so his hours should be divided into both shifts (11am to 4pm in Shift 1, and 4pm to 10pm in Shift 2).

I would then total the number of Shift 1 hours in one cell, and Shift 2 hours in a separate cell.

I need to be able to calculate the number of hours that fall between 8pm and 8am any any one shift.

A shift can be a maximum of 24 hours but the enhanced hours may fall into the following patterns as examples

Start Mon 02:00am finish Tue 01:00am = enhanced hours of 6+5 = 11
Start Mon 14:00pm finish Tue 13:00pm = enhanced hours of 5
Start Mon 14:00am finish Mon 16:00pm = enhanced hours of 0
Start Mon 22:00pm finish Tue 03:00am = enhanced hours of 5
Start Mon 19:00pm finish Tue 04:00am = enhanced hours of 9
Start Mon 23:00am finish Tue 22:00pm = enhanced hours of 5 + 2 = 7

I only want to input times (no dates, 24 hours clock)
Can anyone advise on a formula to help?

I'm currently using Excell 2003 SP3 Professional Edition.

My overall goal is to get a formula that calculates the hours and minutes between to time frames. I've tried finding formulas via this website and others and none of them work. I consider my level of Excel knowledge to be average, so I maybe missing something very easy here.

Example:
AH= 5/2/2011 16:51
AI= 5/5/2011 11:23

I need to only calculate hours and minutes for business hours and exclude weekend/after-hours.

Our business hours are from 7am-6pm, so the hours/minutes should only be calculating between those times.

Any help would be greatly appreciated. I can also send spreadsheet if need be.

Hoping u can help, my problem i, working out hours worked, using a 12hr system, formular i'm using is =sum(finish-start)*24-break, =sum(6:00pm-8:am)*24-1. this works fine till i have a start time of say 3:00pm & a end time 12:00pm (midnight), or a start of 12:00pm (midnight) & end time of 3:00am. i want a one formular does all, as i have a large staff base and their hours/shifts change on a regular bases, so i don't want to keep changing fomulars each time a staff changes their shift i just want to be able to change their hours. hope someone can help. Breaks are formatted as "General" or "Number" as i then calculate O/T pay rates etc. Thanks Phil A

Please can someone help improve on this forumla. At the moment it works for all times except midnight. I am also sure that there must be a more efficient way of writing it. Basicly we pay single rate between 8.00am and 8.00pm at all other times and on bank holidays and weekdays we pay an enhanced rate. If I can calculate the standard hours then the remainder of the hours are enhanced.

Aim to count how many work hours fall between a standard start time (SHS) and a standard end time (SHE) (currently 8.00am and 8.00pm), that do not fall on a weekend and are not a bank holiday.

Column B = Start time (eg 06:00)
Column C - End time (eg 22:00)

Column D= date (from which day of week is derived)
Column E = Y or N for whether it is a bank holiday or not.

Current forumla in column P is
=(IF(ISNONTEXT(D11),(IF(OR(WEEKDAY(D11)=1,WEEKDAY(D11)=7,E11="y"),0,IF(B11<shs,IF(C11<=shs,0,MIN(she ,C11)-shs),0)+IF(AND(B11>=shs,B11<she),(O11/24)-IF(AND(C11>B11,C11>she),C11-she,0)-IF(C11<B11,MIN(C11,shs)+4/24,0),0)+IF(B11>=she,IF(AND(C11<B11,C11>=shs),MIN(C11,she)-shs,0),0))+A11),0)*24)-R11

Some of our shifts start of end at midnight so I do need the formula to work for that time as well as other times.

Thanks
E

nb for a start time of 6.00am and an end time of 22.00 the formula should calc standard hours of 12

for a start time of 3.00am and end time of 10am the formula should count 2

for a start time of 15:00 and and end time of 24:00 the forumla should count 5.

Hi all,

I am having a problem getting my head around the old date/times in excel.

I need to come up with a way to find how many hours are being worked in specific intervals (read: shifts) during a larger time period.

That is, i know that people worked between 7:00am Tuesday and 7:00am Wednesday (ie. 24 hours). I need to know how many hours were worked in the Day Shift (7:00am-3:00pm), the Afternoon Shift (3:00pm-11:00pm) and the Night Shift (11:00pm-7:00am).

My data table looks like this at the moment

DT_START_MOVEMENT DT_END_MOVEMENT Day Shift Afternoon Shift Night Shift 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09 07:00 Tue 30/06/09 07:00 Wed 01/07/09

Note: I'm not an idiot, the intervals between start and end do get more complicated than 7:00 to 7:00 the next day.

Any help on this would be greatly appreciated as ATM i am having a mental breakdown.

Regards,

Thomas Poulton

Hi,

I'd like to take two times in the 24-hour system and work out the difference between them, and then display the hours in one cell and the minutes in another.

So, for example, if A1='17:24:00' and A2='20:16:00', I want that A3='2' and A4='52'. I have a formula which works for calculating the minutes (MINUTES(A2-A1), but I can't figure out how to do the hours. SUM(A2-A1) doesn't work.

Any help would be much appreciated.

Cheers,
doonyakka

I am having trouble trying to get a range of time such as an employee schedule to break down into single units. For example I want this employee's schedule to break down into trackable labor hours.

Monday

IN OUT HOURS Marorie 8:00 AM 6:00 PM 10.00

I have another sheet n my workbook that tracks labor hours over a day and I am trying to get it to populate based upon the schedule tool I use.

Name 5:00am 6:00am 7:00am 8:00am 9:00am 10:00am 11:00am 12:00pm 1:00pm 2:00pm 3:00pm 4:00pm 5:00pm 6:00pm 7:00pm 8:00pm 9:00pm 10:00pm

Marjorie

0.5 1 1 1 1 0.5 1 1 1

8
So basically I want my schedule worksheet to populate my labor hour sheet if Marjie is scheduled from 8:00 am to 6:00pm then a value of 1 populates under each hour she is scheduled.

Any ideas for an easy way to work this?? I have tried numerous IF functions and just cant get it to work.

I know the formula for adding time which is:
+TIME (hours, minutes, seconds).

I need to know the formula/code to add TIME to a cell if the cell passes through a fixed time.

For example:

If cell A1 is 9:00AM and cell A2 is 5:00PM, how do I add 15 minutes to 5:00 to make it 5:15PM without having to manually add it everytime? The 15 minutes would be a break during the working shift.
But if cell A1 is 8:00AM and cell A2 is 10:00AM I would not want to add the 15 minutes.

Any help would be appreciated. Thanks!

Hi
I have a spreadsheet where I record individual staff start time, end times and the length of their break. From this I caculate the hours they worked for the day.

E holds Start, F holds End and G holds Break length.
Thus using the following forumula gives me the number of hours and minutes:

Code:

```=TEXT((E10-F10-G10),"h:mm")
```

However, I find for night shift this does not work. If a staff memeber starts at 20:00 and ends at 06:00 then I get a 'Value' error in the cell.

How can I solve this so that the formula works in both cases?

This is my first post here. I use excel quite a bit for calculations and it would really help me to be able to do the following. I take samples every day (sometimes more than once) and at different times. I would like to have excel calculate the total accumulated hours for me. For example...

Let's say I took 4 samples of whatever...

Sample Dates/Times:

3/24/2010 -- 9:00AM <- 0 hours accumulated
3/24/2010 -- 3:00PM <- 6 hours accumulated
3/25/2010 -- 9:30AM <- 24.5 hours
3/26/2010 -- 9:00AM <- 48 hours

How can I have excel calculate the hours for me? I have tried a few ways and failed. I also poked around the forum for a few minutes but didn't see anything. This would really help me and reduce my workload. Thanks!

Hi,

I am trying to build an automated roster mentioning the requirements below. I will also be attaching a roster that I have manually created for the last 1 year now.

1. Five (5) Shifts: 8:00AM to 5:00PM - Not applicable as of now however want it for scalability.
5:00PM to 2:00AM - W People
7:00PM to 4:00AM - X People
10:00PM to 7:00AM - Y People
2:00AM to 11:00AM - Z People

2. 11 employees right now want to put it scalability of upto 25 employees.
3. 5 days working. (2 week-offs - split or consecutive)
4. Max Leave/Week-Off Preferences/Requests should be honoured, not all.
5. Want an overlaping headcount of of nine (9) people in the 7:00PM to 7:00AM window, 5:00PM to 7:00PM need atleast two (2) people, from 7:00AM to 11:00AM want atleast three (3) people.
6. Breaks: 2 X 15 minutes & 1 X 30 minutes
7. Should Display Employee ID, Reporting Supervisor's name & Employees name
8. Shift Rollover should be every 15 days.

I am adding up the following hours worked in excel payroll sheet. The times are as follows:

Monday 8 hours Entered as 8
Tuesday 8 Hours Entered as 8
Wednesday 8 Hours Entered as 8
Thursday 8 Hours Entered as 8
Friday 7 Hours and 15 Minutes Entered as 8.25
Saturday 8 Hours 45 Minutes Entered as 8.45
Sunday 0

When I add up using the sum formula I get 47.7

How can I get it to calculate properly and say 48 Hours

Is there a formula to subtract times i.e Start 8.00am "Cell A1" and Finished 5.15pm "Cell A2" Answer "Cell A3"

Thanks

Hello,

I am trying to create an excel formula that appropriately calculates the elapsed time from when a ticket was created to when it has been closed out. This is also respecting work hours from 8AM to 5PM, so a 9 hour day, and also respecting weekends.

So, take

OPENED
4/20/11 7:53 PM
.
CLOSED
4/26/11 2:55 PM

Now the opened time is at 7:53PM, so since this is outside of work hours would become 4/21/11 8:00AM to respect the start of work hours. Along with that, it was opened for 5 days after moving it to 8:00AM, so the hours elapsed was 51 hours and 55 minutes, or 51.9 hours in decimal format (which is what I want).

This calculation would be converted into days, also, for different priorities, so hopefully it's possible to calculate the days based on business hours too. So, for an elapsed time of 18 hours, it took 2 days since business hours are 9 hours a day.

Thanks for the help.

Work hours are Monday to Friday, 8AM to 5PM - 9 hours.

Edit: This is also for Excel 2010. For some reason it says 03 up at the top right.

Hello all,

Im trying to create a spreadsheet to track how many hours have been used by an employee. Currently the employee starts with 480 hours and as they use these hours, I am displaying the remainder in days, hours and minutes. This all works fine until I get to 255 hours and then the remaining hours seem to invert, going back to the beginning. For example, if I input that the person has used 256 hours, the end result shows "1 Day, 248 Hours, 0 Minutes". Additionally, if I test the spreadsheet by saying that the person used all 480 hours (60 days), the "Days Used" should be 60, but instead it shows 29 days 248 hours 0 minutes.

This is the formula I am currently using:

=DAY(G2/8)&" days "&(G2/8-DAY(G2/8))*8&" hours "&MINUTE(G2)&" minutes"

Im sure its something simple in my formula, but Im extremely rusty here...

Any help is greatly appreciated!

K

Hi all,

I need an help to calculate SLA by considering office hours only. As of now i'm using this formula =WORKDAY(A2,2,DATE(YEAR(A2),{12,13},{25,1}))+MOD(A2,1) which is calculating non-working hours also.

My office timings are from 09:00AM to 18:00PM.

Any help would be appreciated!!

Elango

I found this information helpful. But I have a different scenario that I am having difficulty calculating regarding performance standards for call center staff. I need to find a calculation that compares the difference between a staff members actual number of hours and minutes and the performance standard hours and minutes which sometimes is negative and I get #####.

For example, the performance standard for talk time a month is 38 hours and 28 minutes and the performance standard is 41 hours and 10 minutes. Does anyone know how of a calculation for this scenario and also if these times were reversed. Thanks.

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.

Hello!

I am currently working on a spreadsheet that will be able to log certain business processes and target times for them to be achieved. Basically, whenever a call comes in, we register it on the spreadsheet then have 3 hours max to reply to it. e.g. call comes in at 10:00am, we have a target of 1:00pm to solve it.

The problem I am facing is we work within regular working hours (9:00am - 5:00pm Monday to Friday), so for instance if a call comes in at 4pm Friday, we have until 11:00am Monday to solve it (an hour on Friday upto 5pm and then 2 hours on Monday from 9am-11am). Hope that makes sense!

I was wondering if there was any formula or code for a button I could use that would incorporate business days and a 9-5 rota for this target??

Any help would be greatly appreciated!!

P.S. I am currently using the formula: =IF(B4="","",B4+TIME(3,0,0)) in order to find the time 3 hours in advance.

I need help on making a formula that calculates our time cards automatically for us.

We have our own method of rounding the hours, so we cannot use any time card templates found online.

Basically we need a formula that calculates TIME IN and TIME OUT, minus 30 minutes for lunch, and then round the remaining number with the following template:
12:00 - 12:09 = 12
12:09 - 12:19 = 12.25
12:19 - 12:29 = 12.33
12:29 - 12:39 = 12.50
12:39 - 12:49 = 12.66
12:49 - 12:59 = 12:75

An example would be
TIME IN TIME OUT
6:00am 5:55pm = 11:55 (MINUS 30 = 11:25) ROUNDED to 11:33

I do not know whether military time would be easier in calculating the sum of hours, as we frequently have some employees that work beyond 12 hour shifts.

This is all easy on paper, yet I haven't a clue on how to calculate any of this on excel.

Thanks for any help.

Dihs

Hi.

Run and search over net got me confused. Made it with IF and MEDIAN and all time got wrong numbers. Got it work when start time (IN) was less or more 6.00AM.
Anyone can help make this simple sheet? Or have right direction to me?

I've reviewed a lot of these postings and can't find anything that can help, so I hope you all can help me out.

This is a crazy complicated way to calculate overtime, but OT must be paid for hours worked over 8 in a workday. The workday is defined as a 24 hour period based on when the employee begins their shift. An example would be an employee clocks in on 11/5/10 12:00pm, break at 4:00pm, back in at 5:00pm and end of shift at 9:00pm for a total hours worked of 8. The employee works the next day on 11/6/10 8:00am, break at 12:00pm, back in at 1:00pm and end of shift at 5:00pm for a total hours worked of 8. The total hours worked in the 24 hour period from when the employee clocked in on 11/5 is 12 hours and therefore 4 hours must be paid as OT.

Does anyone have any idea how to write a formula to calculate this?

I have a timesheet that adds the total hours for a week. For a particular code, an employee worked 32 hours and 15 minutes. In order for me to enter them into our system, I need them in decimal hours, and this case it would be 32.25 hours.

I need a formula to do this automatically. I am using, =IF(R8="","",(R8-INT(R8))*24) which works fine for when there is less than 24 hours, but it won't work in cases like this, and this happens frequently.

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.

Hello,

We have a client who will pay using the 1/4, 1/2/ and 3/4 of an hour increments as follows:

From the hour mark to 6 minutes = 0 time paid
Over 7 minutes to 15 minutes = 1/4 hour 15
16 minutes to 30 minutes = 1/2 hour 30
30 minutes to 45 minutes = 3/4 hour 45
46 minutes to 60 minutes = one hour.

They work on shifts and the operation is 24 hours. Each gets 30 minutes for breaks which is not paid. Pay is biweekly and overtime is anything over 40 hours. Currently, the template we are using looks like this (sorry, I could not figure out how to paste it so you can see it in Excel layout):
WEEK 1 Sunday Monday Tuesday Wednesday Thursday Friday Saturday
5/16 5/17 5/18 5/19 5/20 5/21 5/22
TIME IN ENTER TIME, INCLUDE AM OR PM
TIME OUT
TIME IN
TIME OUT
TIME IN
TIME OUT
TIME IN Total Hours First Week
TIME OUT
HOURS WORKED-OR- choose one
*HOLIDAY WORKED
PAID HOLIDAY (not worked)
*OTHER PAID HOURS
EXPLANATION OF *HOLIDAY WORKED HRS OR *OTHER

I am very new to Excel and need help creating a formula that will allow us to calculate this timesheet? I am in desperate need for help.

Thanks..