Calculating The Number Of Hours And Minutes Between 2 Times 


Calculating The Number Of Hours And Minutes Between 2 Times  Excel 
View Answers 
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(C3B3,"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 ?
I'm using the formula:
=TEXT(C3B3,"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
Round Times To 5 Minutes
 See how to round times to 5 minutes using the CEILING and MROUND functions. ...
Add Time by Hour, Minute or Day
 See how to use formulas to add any unit of time that you would like. Add a column of time values with units of Hours, Minutes, or Days.
1) ...
1) ...
Excel Time Format & Calculations (10 Examples)
 Learn about:
1.What time is in Excel
2.Time in Excel is a decimal between 0 and 1, and the decimal represents the proportion of one 24 hour ...
1.What time is in Excel
2.Time in Excel is a decimal between 0 and 1, and the decimal represents the proportion of one 24 hour ...
Count Times Greater Than 5 M
 See Mr Excel and excelisfun create 5 formulas to count times greater than five minutes. See the Excel functions COUNTIF and TIME. See different syntax ...
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
 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
 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
 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 Popup Windows in Excel
 This is a very simple Message Box, popup window, macro for Excel that illustrates how to put new lines, the same thi
 This is a very simple Message Box, popup 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
 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.
Thanks for your help!!!
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?
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/afterhours.
Our business hours are from 7am6pm, 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.
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/afterhours.
Our business hours are from 7am6pm, 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(finishstart)*24break, =sum(6:00pm8:am)*241. 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),C11she,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.
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),C11she,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:00am3:00pm), the Afternoon Shift (3:00pm11:00pm) and the Night Shift (11:00pm7: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
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:00am3:00pm), the Afternoon Shift (3:00pm11:00pm) and the Night Shift (11:00pm7: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 24hour 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(A2A1), but I can't figure out how to do the hours. SUM(A2A1) doesn't work.
Any help would be much appreciated.
Cheers,
doonyakka
I'd like to take two times in the 24hour 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(A2A1), but I can't figure out how to do the hours. SUM(A2A1) 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.
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!
+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(((I36I37)*60+(J36J37))/60))
The current formula in the minutes column is:
=IF(J37="","",IF(I38=0,MOD(((I36I37)*60+(J36J37)),60),ABS(MOD(((I36I37)*60+(J36J37)),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
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(((I36I37)*60+(J36J37))/60))
The current formula in the minutes column is:
=IF(J37="","",IF(I38=0,MOD(((I36I37)*60+(J36J37)),60),ABS(MOD(((I36I37)*60+(J36J37)),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
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:
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?
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((E10F10G10),"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!
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 weekoffs  split or consecutive)
4. Max Leave/WeekOff 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
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????
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.
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/8DAY(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
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/8DAY(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 nonworking 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(F18E18,1)MOD(I18H18,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.
=IF(COUNT(E18:F18,H18:I18)=4,24*(MOD(F18E18,1)MOD(I18H18,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.
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:000:30:00. ( if only used 10 minutes of break of 30 total put 20 minutes back into expected time so that 8:000: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$40E3)) 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.
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:000:30:00. ( if only used 10 minutes of break of 30 total put 20 minutes back into expected time so that 8:000: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$40E3)) 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 9am11am). 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 95 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 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 9am11am). 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 95 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
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.
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.