To calculate an employee's working hours:
Enter the following formula into a cell:
=D4-C4+IF(C4D4,1)
The number 1 in the IF formula equals 24 when working with time.
For example, in row 6, the calculation is
6.00 - 20.31+ 24.00 = 9.29.
Screenshot // Calculating the Difference Between Hours
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Hi Joe,
All you have to do is subtract one from the other.
For example, using your dates / times above:
B2 contains 6/1/2003 07:00
B3 contains 6/2/2003 13:00
The formula to get the difference between the two is just:
=B3-B2
Obviously the answer we want is one month, 6 hours, and this formula returns 31.25 days (= 31 days, 6 hours).
A month is not a well defined time period (28, 29, 30, or 31 days) so best to avoid ever using it if you can.
The answer of 31.25 days is correct, but most people would prefer 31 days, 6 hours.
To get the cell to display the answer in that *format* (and show minutes for example), just apply the following custom format to the cell:
dd"" days"", h"" hours"", m"" mins""
If you wanted it displayed in just hours (750 hours), use the following format:
[hh]:mm
Hope that helps,
Alan."
"6/1/2003 07:00
6/2/2003 13:00
how can calculate the difference betweent these two times with out having to enter the dates over and over?"
I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 - 18:00, EVE Rate = 18:00 - 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there
"I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Thank you,
Chad."
"How can I set up my excel to calculate my time sheet?
HELP PLEASE"
"Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc."
"I need to convert time into minutes such as (start 5:20pm Stop 5:50 pm = total minutes (30)
please help "
"I have a spreadsheet that I'd like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG - 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours
Thank you very much for your help."
im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total ... eg 9 hours work = 8 pay, 6 work 5.5 pay ... any ideas ??? THANK YOU
I have information from excel that i need to put into microsoft access. I learned how to transfer the information, but how do i get it so if i put in one keyword the information i need pops up?
"Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;""HH.MM"");LEFT(TEXT(A1;""HH.MM"");3);""""))+VALUE(LEFT(TEXT(A1;""HH.MM"");2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions - write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899"
I have a column with hours listed (i.e. 1:00, 1:15, :45, etc.). I need to convert that to just minutes. So 1:00 would equal 60, 1:15 would equal 75, 45 would equal 45. Thank you for your help.
to show local time, use cell format time and select your desired!
I have a time sheet that "shows" military time. I want it to "show" standard time although it needs military time to calculate the time. Same problem as Chad from Freeport, IL
Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?
I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 - start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 - but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly - can anyone help?
Format the cells containg the date : (dd/mm/yyyy hh:mm). Format the cells where the answer will be displayed as follows :([h]:mm). Then just do a normal calculation (e.g. =A2-A1)
"If I have two cells with these two date times. How do I add them together to get
the total hours:min (27 hrs 47 min)
4/12/2003 21:30
4/14/2003 1:17"
Just format the column to be added as 00:00 then use the SUM() function to tally up the hours and minutes
I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?
"Hi Joe,
All you have to do is subtract one from the other.
For example, using your dates / times above:
B2 contains 6/1/2003 07:00
B3 contains 6/2/2003 13:00
The formula to get the difference between the two is just:
=B3-B2
Obviously the answer we want is one month, 6 hours, and this formula returns 31.25 days (= 31 days, 6 hours).
A month is not a well defined time period (28, 29, 30, or 31 days) so best to avoid ever using it if you can.
The answer of 31.25 days is correct, but most people would prefer 31 days, 6 hours.
To get the cell to display the answer in that *format* (and show minutes for example), just apply the following custom format to the cell:
dd"" days"", h"" hours"", m"" mins""
If you wanted it displayed in just hours (750 hours), use the following format:
[hh]:mm
Hope that helps,
Alan."
"6/1/2003 07:00
6/2/2003 13:00
how can calculate the difference betweent these two times with out having to enter the dates over and over?"
I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 - 18:00, EVE Rate = 18:00 - 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there
"I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Thank you,
Chad."
"How can I set up my excel to calculate my time sheet?
HELP PLEASE"
"Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc."
"I need to convert time into minutes such as (start 5:20pm Stop 5:50 pm = total minutes (30)
please help "
"I have a spreadsheet that I'd like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG - 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours
Thank you very much for your help."
im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total ... eg 9 hours work = 8 pay, 6 work 5.5 pay ... any ideas ??? THANK YOU
I have information from excel that i need to put into microsoft access. I learned how to transfer the information, but how do i get it so if i put in one keyword the information i need pops up?
"Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;""HH.MM"");LEFT(TEXT(A1;""HH.MM"");3);""""))+VALUE(LEFT(TEXT(A1;""HH.MM"");2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions - write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899"
I have a column with hours listed (i.e. 1:00, 1:15, :45, etc.). I need to convert that to just minutes. So 1:00 would equal 60, 1:15 would equal 75, 45 would equal 45. Thank you for your help.
to show local time, use cell format time and select your desired!
I have a time sheet that "shows" military time. I want it to "show" standard time although it needs military time to calculate the time. Same problem as Chad from Freeport, IL
Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?
I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 - start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 - but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly - can anyone help?
Format the cells containg the date : (dd/mm/yyyy hh:mm). Format the cells where the answer will be displayed as follows :([h]:mm). Then just do a normal calculation (e.g. =A2-A1)
"If I have two cells with these two date times. How do I add them together to get
the total hours:min (27 hrs 47 min)
4/12/2003 21:30
4/14/2003 1:17"
Just format the column to be added as 00:00 then use the SUM() function to tally up the hours and minutes
I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?
http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
"Hi John,
I don't understand what you want to achieve.
Please could you elaborate a little.
Specifically, you ask if there is some way to change 07:00 to 0700, but at the same time you talk about faster inputting. I would suggest that inputting 0700 is actually faster than typing the extra character to get 07:00, so you would actually be better of just going direct to input 0700.
You also mention a chart. What are you charting?
Thanks,
Alan."
The spreadsheet I am using has time listed as 07:00. I want to be able to ten key in the times. Is there a lookup formula that could change the 07:00 to 0700? If so how do I get the one cell to reference the chart?
"A1 and A2 are times. Ex 12:00:00 and 12:10:00
So I want C2 to equal 3 different things:
If A1 =A2 I want C3 to say On Time
If A1
If A1>A2 I want C3 to say Early
I tried several formulas but it just returns #VALUE!
I thought the correct formula should be:
=IF(A1=A2,”On Time”)+IF(A1>A2,”Early”)+IF(A1
Please Help "
I am having problems figuring out how to set my timesheet up. The times are note figuring out correctly and the formulas are note working. HELP I Day In Out In Out In Out Regular Hours Overtime Hours Make Up Hours Holiday Hours Vacation Sick Other Hours Type Total Hours at Other Rate
"Hi Chris,
If you have those times in cells A1 and B1, then the following formula will do it:
=IF(B1-A1<((1/24)/60),"""",B1-A1)
I have assumed that the times are entered using Excel's date / time values, not as, for example, text.
Alan."
How do I write a formula to calculate the difference of 2 cells containing times, but only if the difference is greater than 1 min. example: a1 is 12:00:00 and a2 is 12:01:35 I want b1 to show the difference of 0:01:35, but if a2 is only 12:00:27 I dont want b1 to show the anything
"Hi Chad,
The basic trick here is to remember that Excel likes to work in days (being equal to 24 hours) as its default time unit.
>I am working on a spread sheet that I need to
>calculate an average time it takes to do something (in
>minutes) (i.e. Number of Trays Total / Average Trays
>Per Minute = Minutes it Takes to Fill Trays).
Okay - to calculate the average time that it takes to do something:
A1 = Total time = 2 hours = 2/24 of a day = 0.083333....
A2 = Total 'events' = 24
A3 = Average time = A1/A2 = 5 minutes = (2/24)/24 = 2/576 of a day = 0.00347222...
If you format cell A3 appropriately, it could display as 0:05 for example - see reply above.
>Next, I need to add the mintues to a time(i.e. 6:30 AM
>+ 5 minutes (from calculation above) = 6:35 AM).
>Finally I need to add more minutes (time it takes for
>someone to push a cart from kitchen to hospital unit)
>to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Okay, 6:30am will always be 6.5 hours past midnight = N+(6.5/24) where N is any whole number representing the date that you want. If N=0, then Excel will assume you mean 0 Jan 1900 (!), however, perhaps you never actually care about the date?
So:
A5 = (6.5/24) = 0.2708333.... Representing 6:30am (you can format as you prefer - see above)
A3 = 5 Mins = (5/60)/24 = 0.00347222.... (from above)
A6 = A5+A3 = (6/24)+(((30+5)/60)/24) = 0.27430555... = 6:35 hours past midnight
Hope that all makes sense!
Alan."
"Hi Joe,
All you have to do is subtract one from the other.
For example, using your dates / times above:
B2 contains 6/1/2003 07:00
B3 contains 6/2/2003 13:00
The formula to get the difference between the two is just:
=B3-B2
Obviously the answer we want is one month, 6 hours, and this formula returns 31.25 days (= 31 days, 6 hours).
A month is not a well defined time period (28, 29, 30, or 31 days) so best to avoid ever using it if you can.
The answer of 31.25 days is correct, but most people would prefer 31 days, 6 hours.
To get the cell to display the answer in that *format* (and show minutes for example), just apply the following custom format to the cell:
dd"" days"", h"" hours"", m"" mins""
If you wanted it displayed in just hours (750 hours), use the following format:
[hh]:mm
Hope that helps,
Alan."
"6/1/2003 07:00
6/2/2003 13:00
how can calculate the difference betweent these two times with out having to enter the dates over and over?"
I am working using functions IF statements and I need to be able to calculate the duration between different times. Day Rate = 08:00 - 18:00, EVE Rate = 18:00 - 08:00 and Wkend Rate = Midnight Fri to Midnight Sun. So for a call starting on Thursday 17:00 and ending on Friday 19:00 would be charged for 1hr at day rate and 1hr at evening rate. How can i display this in Excel. I can break it down into many cells as poss if need be. I am finding this impossible, any experts out there
"I am working on a spread sheet that I need to calculate an average time it takes to do something (in minutes) (i.e. Number of Trays Total / Average Trays Per Minute = Minutes it Takes to Fill Trays).
Next, I need to add the mintues to a time(i.e. 6:30 AM + 5 minutes (from calculation above) = 6:35 AM).
Finally I need to add more minutes (time it takes for someone to push a cart from kitchen to hospital unit) to the time we just got (i.e. 6:35 AM + 5 Minutes = 6:40 AM).
Thank you,
Chad."
"How can I set up my excel to calculate my time sheet?
HELP PLEASE"
"Lori,
You have to explain to Excel that one day has 24 hours. So, first you have to format the cell as [h]:mm. It will show 5:15 in cell A1, for instance. Then, in cell A2, you multiply A1 for 24, and you will have 5,25 hours. Note that the format in cell A2 is general number.
This tip can also be very helpful for those who need to calculate timesheets, employee payments, etc."
I need to convert time into minutes such as (start 5:20pm Stop 5:50 pm = total minutes (30) please help
I have a spreadsheet that I'd like to show times that include minutes as 1/4 of an hour, or 1/2 of an hour. EG - 5:15 shows as 5.25 hours; 5:30 shows as 5.5 hours; 5:45 shows as 5.75 hours Thank you very much for your help.
im trying to write a formula that calculate the difference between the start and finish of my staff as well as taking the break time off the total ... eg 9 hours work = 8 pay, 6 work 5.5 pay ... any ideas ??? THANK YOU
I have information from excel that i need to put into microsoft access. I learned how to transfer the information, but how do i get it so if i put in one keyword the information i need pops up?
"Here is my decision:
1. write in column A (for example) hours listed (i.e. 1:00, 1:15, :45, etc.)
2. in cell B1 (or any other you want) write this formula: =VALUE(SUBSTITUTE(TEXT(A1;""HH.MM"");LEFT(TEXT(A1;""HH.MM"");3);""""))+VALUE(LEFT(TEXT(A1;""HH.MM"");2))*60
3. Copy & Paste the formula as much as it is necessary
If you have any problems or questions - write to my e-mail: koliu_m@yahoo.com or ICQ: 56468899"
I have a column with hours listed (i.e. 1:00, 1:15, :45, etc.). I need to convert that to just minutes. So 1:00 would equal 60, 1:15 would equal 75, 45 would equal 45. Thank you for your help.
to show local time, use cell format time and select your desired!
I have a time sheet that "shows" military time. I want it to "show" standard time although it needs military time to calculate the time. Same problem as Chad from Freeport, IL
Is there an easy way to convert hours to read as standard time rather than military time? I am automating time sheets for my emplyees and I am finding it difficult to show 4:00 PM rather than 16:00 PM. -anyone?
I need to work out difference between 2 dates and times in excel, but in working hours only, ignoring off duty hours (Weekends are working days). i.e working hours are 0800 to 2000 - start date/time 21/04/03 09:00 end date/time 22/04/03 17:00 - but for some rows the actual start time is prior to 08:00 and on others the end time is after 20:00 so the formulas I have tried so far havent worked properly - can anyone help?
Format the cells containg the date : (dd/mm/yyyy hh:mm). Format the cells where the answer will be displayed as follows :([h]:mm). Then just do a normal calculation (e.g. =A2-A1)
"If I have two cells with these two date times. How do I add them together to get
the total hours:min (27 hrs 47 min)
4/12/2003 21:30
4/14/2003 1:17 "
Just format the column to be added as 00:00 then use the SUM() function to tally up the hours and minutes.
I flew in the RAF for 27 years and have recently tried to audit my logbook additions. The times are in Hrs and 5min increments. It is cumbersome so I was interested in this tip. However, the wrong spreadsheet appears to have been added to this tip. It is the same as for the previous tip and does not relate to the text describing the tip. e.g. cells E4:E8 is hh:mm. Do you have a way of adding columns of hrs and minutes?