Excel formula help please !

Emp_Fab

I'm trying to subtract one elapsed time from another, where the times are in the format of 1.12.43 (i.e. 1 minute, 12.43 seconds)

None of the times are longer than ten minutes.

I've been playing around with the cell formatting for ages and I'm getting some seriously odd results !  

Say, I've got 1.12.43 in cell A1 and 50.00 in cell A2 (i.e. 50 seconds), what cell format should I be using (is it even important for the formula ?) and what formula should I be using to get the result of 22.43 (i.e. A1-A2) ??

I'm stumped !  Ta !!

Teetonetal

Format your Times to 1:12:43 and 0:50:00
Then format sells to time, using that exact format
Then Formula =SUM(A1-A2) (also in time cell format)

Result

0:22:43

Or format all 3 cells to CUSTOM
H:MM:SS

Of course I misread that you were looking for 1 min 12.43 seconds... although I suppose it works as a factor?

paul_c2

1. Make sure both/all cells are in date/time format (Cells --> Formatting or whatever). Also make sure they're wide enough to properly display.
2. The value "1" is used to define 1 day, so a time of eg 10 minutes, is going to be 1/24 x 10/60 = 0.0069444444 . I think once you've set the formatting properly, you can enter them in the form 00:10:00 or so

The value 50.00 will be interpreted as 50 days

stickyfiddle

Time formatting is excel is a nightmare.

Convert the whole lot into seconds as a number then do you calculation, then convert back to mm:ss

Sporky

Teetonetal said:

Then Formula =SUM(A1-A2) (also in time cell format)

Give your copy of Excel back to Microsoft right now.

The SUM() function sums (ie adds together) its arguments, it is not a generic envelope function.

For the above the correct syntax is =A1-A2

Emp_Fab

That's what I was just about to say !

Either way, formatting the cells as 'time' doesn't work, I have to specify custom, then mm.ss.00 to get it to display correctly.  Subtracting is still a puzzle though - I keep getting #VALUE! errors.

FX_Munkee

Go programmer. Convert the times to milliseconds, do sum. Convert back to "time for people who can't understand milliseconds"

Teetonetal

Sporky said:

Teetonetal said:

Then Formula =SUM(A1-A2) (also in time cell format)

Give your copy of Excel back to Microsoft right now.

The SUM() function sums (ie adds together) its arguments, it is not a generic envelope function.

For the above the correct syntax is =A1-A2

Still produces the right result though. The question wasn't how do I do it correctly.

stickyfiddle

Teetonetal said:

Sporky said:

Teetonetal said:

Then Formula =SUM(A1-A2) (also in time cell format)

Give your copy of Excel back to Microsoft right now.

The SUM() function sums (ie adds together) its arguments, it is not a generic envelope function.

For the above the correct syntax is =A1-A2

Still produces the right result though

True, but if i was auditing your model it'd be in my issues list...

mynamesnotbob

Format with Colons to separate. If you have a big list, text to columns or use the LEFT argument to split on the character. Then take those 3 cells, and concatenate back into a correctly formatted cell with colons.

Once you have that it's simply =TEXT(B2-A2,"h:mm:ss"). Where B2 and A2 are the cells you need

Sporky

Teetonetal said:

Still produces the right result though

But on really large spreadsheets it results in slower recalculation and larger files. And it's sloppy.

Emp_Fab said:

That's what I was just about to say !

Either way, formatting the cells as 'time' doesn't work, I have to specify custom, then mm.ss.00 to get it to display correctly.  Subtracting is still a puzzle though - I keep getting #VALUE! errors.

Two approaches occur to me.

1) Divide the data into two cells, one with the minutes, one with the seconds. Use a simple formula to convert that to seconds (along the lines of =(60*A1)+B1 ), do the comparison in seconds, then convert the outcome back to minutes and seconds.

2) Have your custom format as in your first post, and use LEFT() and SEARCH() and LENGTH() and RIGHT() to determine the minutes from the seconds, convert the whole lot to seconds then as (1).

Emp_Fab

mynamesnotbob said:

Format with Colons to separate. If you have a big list, text to columns or use the LEFT argument to split on the character. Then take those 3 cells, and concatenate back into a correctly formatted cell with colons.

Once you have that it's simply =TEXT(B2-A2,"h:mm:ss"). Where B2 and A2 are the cells you need

@mynamesnotbob  Nice one !  As I'm working with minutes, seconds and hundredths of seconds, I just modified your formula to =TEXT(B2-A2,"mm:ss.00") and, as long as my data fields follow the same format, including leading zeroes, it works a treat !  Thanks mate.

Thanks to everyone else who contributed too.

I don't truly understand what the TEXT function does, but the formula works.

Sporky

Emp_Fab said:

I don't truly understand what the TEXT function does, but the formula works.

It allows you to force the formatting of the result of a calculation.

paul_c2

Sounds like a lot of fannying around, that Excel can you for you once you understand formatting the cells as time format and view/enter it as such.

scrumhalf

TEXT is brilliant for using in reports when you want to include the contents of several cells into a sentence, and to have them formatted correctly.