If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculate time difference to the half hour
I am creating an employee time sheet that the put in their starting time and
ending time (From a drop down list). I would like Excel to calculate the amount of time showing full and if need be a half hour. My problem is that the results half only been in full hours. Such as 9 am to 5 pm comes out as 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5 hours. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h") =(INT(F10-E10*24)) =hour(f10-E10) Any ideas on getting the results I need? Thanks, Ken |
#3
|
|||
|
|||
Don,
Thanks for your help. I tried this a few different ways with out getting the results I'm looking for. Any other ideas? Ken "Don Guillett" wrote in message ... try this =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0) -- Don Guillett SalesAid Software "Ken Ivins" wrote in message ... I am creating an employee time sheet that the put in their starting time and ending time (From a drop down list). I would like Excel to calculate the amount of time showing full and if need be a half hour. My problem is that the results half only been in full hours. Such as 9 am to 5 pm comes out as 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5 hours. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h") =(INT(F10-E10*24)) =hour(f10-E10) Any ideas on getting the results I need? Thanks, Ken |
#4
|
|||
|
|||
Okay, This seemed to work.
=((TEXT(F10-E10,"h:mm"))-INT(TEXT(F10-E10,"h:mm")))*24 Anyone see a problem with this? Thanks, Ken "Ken Ivins" wrote in message ... Don, Thanks for your help. I tried this a few different ways with out getting the results I'm looking for. Any other ideas? Ken "Don Guillett" wrote in message ... try this =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0) -- Don Guillett SalesAid Software "Ken Ivins" wrote in message ... I am creating an employee time sheet that the put in their starting time and ending time (From a drop down list). I would like Excel to calculate the amount of time showing full and if need be a half hour. My problem is that the results half only been in full hours. Such as 9 am to 5 pm comes out as 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5 hours. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h") =(INT(F10-E10*24)) =hour(f10-E10) Any ideas on getting the results I need? Thanks, Ken |
#5
|
|||
|
|||
Ken,
It's unclear what format you want the result to be in. Try: =INT((F10-E10)*48)/2 and format as a number, or =INT((F10-E10)*48)/48 and format as time. Tim C "Ken Ivins" wrote in message ... Okay, This seemed to work. =((TEXT(F10-E10,"h:mm"))-INT(TEXT(F10-E10,"h:mm")))*24 Anyone see a problem with this? Thanks, Ken "Ken Ivins" wrote in message ... Don, Thanks for your help. I tried this a few different ways with out getting the results I'm looking for. Any other ideas? Ken "Don Guillett" wrote in message ... try this =ROUND(A1/TIME(0,30,0),0)*TIME(0,30,0) -- Don Guillett SalesAid Software "Ken Ivins" wrote in message ... I am creating an employee time sheet that the put in their starting time and ending time (From a drop down list). I would like Excel to calculate the amount of time showing full and if need be a half hour. My problem is that the results half only been in full hours. Such as 9 am to 5 pm comes out as 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5 hours. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h") =(INT(F10-E10*24)) =hour(f10-E10) Any ideas on getting the results I need? Thanks, Ken |
#6
|
|||
|
|||
On Tue, 25 Jan 2005 11:46:09 -0500, "Ken Ivins" wrote:
I am creating an employee time sheet that the put in their starting time and ending time (From a drop down list). I would like Excel to calculate the amount of time showing full and if need be a half hour. My problem is that the results half only been in full hours. Such as 9 am to 5 pm comes out as 8 hours. But also 9 am to 4:30 pm also comes out as 7 hours instead of 7.5 hours. I have tried three types of formulas with the same result: =TEXT(F11-E11,"h") =(INT(F10-E10*24)) =hour(f10-E10) Any ideas on getting the results I need? Thanks, Ken Assumption is that your times are entered as Excel times; eg. 9 AM; 4:30 PM; etc. If you wish to round to the nearest 30 minutes, then: =ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0) If you wish to round up to the next 30 minutes, so that 7:01 -- 7:30, then =CEILING(EndTime-StartTime,TIME(0,30,0)) and format as [h]:mm. If you wish to convert either of the above to decimal numbers, then multiply the result by 24: =24 * ROUND((EndTime-StartTime)/TIME(0,30,0),0)*TIME(0,30,0) or =24 * CEILING(EndTime-StartTime,TIME(0,30,0)) --ron |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculate Time in Word Tables | kaykayIT | Tables | 1 | December 8th, 2004 04:05 AM |
Rounding time to nearest 1/4 hour | Hank-B | New Users | 5 | November 24th, 2004 04:48 PM |
Create a timecard that can calculate time on a 12 hour format? | Stumped! | General Discussion | 2 | October 8th, 2004 07:01 AM |
trying to calculate time | pwf3 | New Users | 2 | June 6th, 2004 05:23 AM |
Time difference | Alien1155 | Worksheet Functions | 0 | March 22nd, 2004 05:16 PM |