Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Calculating The Number Of Hours And Minutes Between 2 Times

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

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 Video Tutorials

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
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
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
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

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.


Thanks for your help!!!


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,
Please help. I have a spreadsheet (that someone else wrote years ago) that calculates time worked against contracted time that works fine as long you work the same or more hours than you're contracted for. If you haven't work all your contracted hours it gets it wrong because it has to subtract a larger number of hours and minutes from a smaller one. The hours and minutes are in two separate columns.
The current formula in the hours column is:
=IF(I37="","",TRUNC(((I36-I37)*60+(J36-J37))/60))

The current formula in the minutes column is:
=IF(J37="","",IF(I38=0,MOD(((I36-I37)*60+(J36-J37)),60),ABS(MOD(((I36-I37)*60+(J36-J37)),60))))

I36 is hours actually worked J36 minutes actually worked
I37 is hours that should have been worked, J37 the minutes

There must be a simpler way that actually works for all occasions. Help!

Thanks for looking,
Ric


I have a problem!!! Please help!!!
I have a chart

--A-- project --B-- Production time needed in hours --C-- start date --D-- end date

1-- Mount R. 2 1/2/05 7:00AM 1/2/05 9:00AM

2-- Mount R. 9 1/2/05 9:00AM 1/3/05 8:00AM

what formula can i put in D2 to get this answer. Our working hours are 7:00AM
to 3:00PM. I need it to calculate only working hours to estimate the fnishing date of this project. can you help!
Thank you!
Melanie


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


Help. I am trying to put a formula together to add up the hours and minutes from one time to another. For example:

4/13/09 at 12:33PM to 4/20/09 at 06:00am

What formula in excel should I use to get the number of hours and minutes between the two date????


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 am using this formula to calculate employers hours.

=IF(COUNT(E18:F18,H18:I18)=4,24*(MOD(F18-E18,1)-MOD(I18-H18,1)),0)

I have four columns i.e E=Time in , F=Time out, H=lunch in, I=lunch out

Purpose of above mentioned formula is calculate total hours and to get "0" if employee forget to "time in", "timeout" or "lunch in" or "lunch out".

I am trying to get "0" only if employee forget either "time in" and "time out OR "lunchin" and "Lunch out". e.g

Column Column Column Column
E F H I Total hours
(time in) (timeout) (lunch in) (Lunch out)
1) 10:00AM 6:00PM - - 8

2) 10:00AM 6:00PM 3:00PM 3:30PM 7.5

3) 10:00AM - 3:00PM 3:30PM 0

2) 10:00AM 6:00PM 3:00PM - 0

Currently getting "0" if any of the columns missing time.Which is useless because if employee don't take lunch break then I suppose to get total hours worked not "0"

Appreciate if somebody help me to solve this problem.


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


Trying to get excel to look at the name above and then add the time up from the cell below

Example:
Monday
C1=John
C2=8:00am D2=4:00pm

Tuesday
C3=Mike
C4=8:00am D4=4:00pm

Wednesday
C5=John
C6=8:00am D6=4:00pm

Thursday
C7=Mike
C8=8:00am D8=4:00pm

Friday
C1=Mike
C2=8:00am D2=4:00pm

What I’m trying to get is excel to look at the name and then add the time to the right person.

Cell Total hours
M1=Mike N1=24
M2=John N2=16

This changes all a lot with the time and who works what day
Looking for a manual way to do this

Any help on this will be great thank you.