Calculating two numbers in one cell

  • Thread starter Thread starter chefcasey
  • Start date Start date
C

chefcasey

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
 
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
 
Chef... please, use two cells. Excel won't know what you mean. Is that
7 am - 3pm or 7pm - 3 am?
 
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
 
=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,
 
Shorty: working hours from 7-3 is actually 8 hours!

This really won't work without convoluting his data in another formul
where (3+12)-7=8 will meet his needs.

=(right(A1,1)+12)-left(A1) and this only works for end times that ar
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 dat
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

Bruc
 
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
 
Hi!
It gets really ugly....
Click to expand...

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:
 
Argh!

Hit send before I was done!
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:
Click to expand...

=(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
 
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.
 
Back
Top