|
Calculating two numbers in one cell
I'm working on a work schedule. I would like to put two numbers that represent time in a single cell and have them caculated to total the amount of hours worked. example: 7-3 -- chefcasey ------------------------------------------------------------------------ chefcasey's Profile: http://www.excelforum.com/member.php...o&userid=27198 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Hi!
Make it easy on yourself and use 2 cells. It'll also be less of a headache to enter the times as times: A1 = 7:00 AM B1 = 3:00 PM =(B1-A1+(B1<A1))*24 Biff "chefcasey" wrote in message ... I'm working on a work schedule. I would like to put two numbers that represent time in a single cell and have them caculated to total the amount of hours worked. example: 7-3 -- chefcasey ------------------------------------------------------------------------ chefcasey's Profile: http://www.excelforum.com/member.php...o&userid=27198 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Chef... please, use two cells. Excel won't know what you mean. Is that 7 am - 3pm or 7pm - 3 am? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Put an equals sign in front and Excel will treat it as a formula. So =7-3 will display 4. If you want to format it so Excell displays it in one of the many time formats, then you have to learn to think of time in terms of fractions of a day. So (assuming those are hours) =7/24-3/24 then formatted as [h]:mm:ss will display 04:00:00 -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
=VALUE(TRIM(LEFT(A2, FIND("-", A2,1)-1))) - VALUE(TRIM(MID(A2, FIND("-",
A2,1)+1, 5))) This will only work if the separator is always "-". As is, it assumes that the 2nd value in A2 will not be greater than 5 characters, including decimal (ie., a max of 99.99, or 999.9, etc.) As long as the length of the 2nd value is 5 characters or less, it will handle decimal values in either position. HTH, -- George Nicholson Remove 'Junk' from return address. "chefcasey" wrote in message ... I'm working on a work schedule. I would like to put two numbers that represent time in a single cell and have them caculated to total the amount of hours worked. example: 7-3 -- chefcasey ------------------------------------------------------------------------ chefcasey's Profile: http://www.excelforum.com/member.php...o&userid=27198 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Shorty: working hours from 7-3 is actually 8 hours! This really won't work without convoluting his data in another formula where (3+12)-7=8 will meet his needs. =(right(A1,1)+12)-left(A1) and this only works for end times that are less than the start time (3<7). try 5-12...it fails Also, simply entering 7-3 in General format, Excel will treat as a date 7-Jul No one ever starts at 7:30? It gets really ugly.... I agree with Biff...use two cells and a third for calculations Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Hi!
I'm thinking the OP meant that 7 is the start time and 3 is the end time. So, even if you reverse the order of your formula it still returns the incorrect result. It returns either 4 or -4 when I'm pretty sure that the correct answer should be 8. Better for the OP to use 2 cells and real time values. Biff "George Nicholson" wrote in message ... =VALUE(TRIM(LEFT(A2, FIND("-", A2,1)-1))) - VALUE(TRIM(MID(A2, FIND("-", A2,1)+1, 5))) This will only work if the separator is always "-". As is, it assumes that the 2nd value in A2 will not be greater than 5 characters, including decimal (ie., a max of 99.99, or 999.9, etc.) As long as the length of the 2nd value is 5 characters or less, it will handle decimal values in either position. HTH, -- George Nicholson Remove 'Junk' from return address. "chefcasey" wrote in message ... I'm working on a work schedule. I would like to put two numbers that represent time in a single cell and have them caculated to total the amount of hours worked. example: 7-3 -- chefcasey ------------------------------------------------------------------------ chefcasey's Profile: http://www.excelforum.com/member.php...o&userid=27198 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Hi!
It gets really ugly.... It's not "too" ugly, but the whole idea is to make things as easy as possible. In other words, KISS! I would much rather use 2 cells and enter real times and use: "swatsp0p" wrote in message ... Shorty: working hours from 7-3 is actually 8 hours! This really won't work without convoluting his data in another formula where (3+12)-7=8 will meet his needs. =(right(A1,1)+12)-left(A1) and this only works for end times that are less than the start time (3<7). try 5-12...it fails Also, simply entering 7-3 in General format, Excel will treat as a date 7-Jul No one ever starts at 7:30? It gets really ugly.... I agree with Biff...use two cells and a third for calculations Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Argh!
Hit send before I was done! It gets really ugly.... It's not "too" ugly, but the whole idea is to make things as easy as possible. In other words, KISS! I would much rather use 2 cells and enter real times and use: =(B1-A1+(B1<A1))*24 Consider this: 7-8 Is the difference 1 hour or 13 hours? That's where the problem comes into play. Biff "Biff" wrote in message ... Hi! It gets really ugly.... It's not "too" ugly, but the whole idea is to make things as easy as possible. In other words, KISS! I would much rather use 2 cells and enter real times and use: "swatsp0p" wrote in message ... Shorty: working hours from 7-3 is actually 8 hours! This really won't work without convoluting his data in another formula where (3+12)-7=8 will meet his needs. =(right(A1,1)+12)-left(A1) and this only works for end times that are less than the start time (3<7). try 5-12...it fails Also, simply entering 7-3 in General format, Excel will treat as a date 7-Jul No one ever starts at 7:30? It gets really ugly.... I agree with Biff...use two cells and a third for calculations Bruce -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
Agreed, for some reason I missed the fact that we where talking about
timesheets. I retract my post! Does not address the OP's need and is a bad idea to boot. -- George Nicholson Remove 'Junk' from return address. "Biff" wrote in message ... Hi! I'm thinking the OP meant that 7 is the start time and 3 is the end time. So, even if you reverse the order of your formula it still returns the incorrect result. It returns either 4 or -4 when I'm pretty sure that the correct answer should be 8. Better for the OP to use 2 cells and real time values. Biff "George Nicholson" wrote in message ... =VALUE(TRIM(LEFT(A2, FIND("-", A2,1)-1))) - VALUE(TRIM(MID(A2, FIND("-", A2,1)+1, 5))) This will only work if the separator is always "-". As is, it assumes that the 2nd value in A2 will not be greater than 5 characters, including decimal (ie., a max of 99.99, or 999.9, etc.) As long as the length of the 2nd value is 5 characters or less, it will handle decimal values in either position. HTH, -- George Nicholson Remove 'Junk' from return address. "chefcasey" wrote in message ... I'm working on a work schedule. I would like to put two numbers that represent time in a single cell and have them caculated to total the amount of hours worked. example: 7-3 -- chefcasey ------------------------------------------------------------------------ chefcasey's Profile: http://www.excelforum.com/member.php...o&userid=27198 View this thread: http://www.excelforum.com/showthread...hreadid=467241 |
| All times are GMT +1. The time now is 04:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com