Rounding time

[Supernedz]

Hi, I am building a tool in Excel to calculate timing and penalties to the second in motorsport events. I have a calculation to sum two times, either of which could be positive or negative. I am using the 1904 setting which was necessary for another part of the sheet.

So the cell entries might look like this for example;

A1 = -01/01/1904 00:01:15
B1 = 01/01/1904 00:06:20

Adding the two cells (=A1+B1) works as expected and in this case would be 01/01/1904 00:05:05.

What I need to be able to do though is, given a certain condition (using an IF formula), I need to be able to remove any seconds and round down the total so that the example above would be 01/01/1904 00:05:00.

I know when the sum is positive I want to round down to the nearest minute so 01/01/1904 00:05:05 would become 01/01/1904 00:05:00 and I think when the sum is negative I also need to round down so -01/01/1904 00:09:30 would become -01/01/1904 00:10:00.

I have tried various versions of FLOOR and ROUNDDOWN and TIME and can’t get any of them to behave properly. ROUNDDOWN and TIME result in errors I think caused by negative values and FLOOR has an issue where if the seconds are shown as 00 it will round down to the next minute rather than just leaving the result alone. I think this is to do with the long decimal “real” numbers behind time format.

I have run out of ideas on how to solve this, please help!

 

[KRice]

I don't understand the rounding rules or what negative times look like, but suppose you have an unrounded answer...as shown in A4:B4. You could write your own rounding rules by using the HOUR, MINUTE, and SECOND functions...and then apply logical tests as necessary to round up or down, In A5, any number of seconds > 30 result in a TRUE, which resolves to 1, so 1 would be added to the MINUTE(A4) result (in this case, 5 seconds in the resulting time does not meet that criteria, so the logical test results in 0). In B5, the number of seconds in the result is greater than 30, so the same formula rounds up. In C5, such a test is not included, and we drop all seconds, regardless of their nearness to 0 or 60, so 05:58 becomes 05:00.

Book4
	A	B	C
1	1/1/1904 0:01:15	1/1/1904 0:01:15	1/1/1904 0:01:01
2	1/1/1904 0:06:20	1/1/1904 0:06:47	1/1/1904 0:06:59
3
4	1/0/1900 0:05:05	1/0/1900 0:05:32	1/0/1900 0:05:58
5	1/0/1900 0:05:00	1/0/1900 0:06:00	1/0/1900 0:05:00
Sheet1

Cell Formulas
Range	Formula
A4:C4	A4	=A2-A1
A5:B5	A5	=TIME(HOUR(A4), MINUTE(A4)+(SECOND(A4)>30),0)
C5	C5	=TIME(HOUR(C4), MINUTE(C4),0)

 

[felixstraube]

As you surely know datetime data type are just numbers that represent days. With the 1900 system the number 1 represent 1/1/1900, 2 represent 2/1/1900. In the 1904 system the 1 represent the 1/1/1904 and so on.
The important thing is that the unity (1) in both cases, is a day or 24 hours. So one hour is represented by a 1/24 = 0.0416666...
Here I show in column A the time entered as such, in column B the same value but formatted as General, and then in column D the calculation dividing 1 (a day) in the number of hour, minutes or sec and you see that you get the same result (sorry i dont have XL2BB right now)

This means that if you want a number that represents 12 hours for example it will be 0.5.

Now you want to round to the minute then you can do the following:

1)
Use MROUND and as the second argument use the number that represent a minute which you get with TIME(0,1,0).

=MROUND(A1,TIME(0,1,0))

2) Or you can multiply the time you have (that are in day units) by 24 and then by 60 so it will be in Minutes, round them with 0 digits, and divide it back by 24 and 60.

=ROUND(A1*24*60,0)/24/60

(of course you can replace 24*60 by the result an it becomes:

=ROUND(A1*1440,0)/1440

I personally like the previous better because it is more readable.

 

[felixstraube]

Please note that excel can not represent negative time.
You will get the #

The value in the cell is the negative time but you can't see it. You will have to try some workarounds. Something like this for example:

=IFERROR(TEXT(A1,"hh:mm"),"-"&TEXT(-A1,"hh:mm"))

 

[Supernedz]

Thank you both for your answers, I am afraid I can’t quite figure a solution from them.
I need a formula that basically removes the seconds from a time so if the time is positive it goes from

this 01/01/1904 00:05:21 to this 01/01/1904 00:05:00

and if the time is negative it goes from this -01/01/1904 00:03:56 to this -01/01/1904 00:04:00.

I am sure in both cases that is rounding down.

Can anyone write me a suitable formula please?

 

[felixstraube]

Apply my formula and see.
And you didn't read it thoroughly i think. The part that excel can't show your negative times...

 

[felixstraube]

Formula being in B2 and copy down:

=ROUND(A2*24*60,0)/24/60

It doesn't matter the format it is in. Change it to your format and you'll get the same result.

 

[Alex Blakenburg]

I need a formula that basically removes the seconds from a time so if the time is positive it goes from
this 01/01/1904 00:05:21 to this 01/01/1904 00:05:00
and if the time is negative it goes from this -01/01/1904 00:03:56 to this -01/01/1904 00:04:00.

I am sure in both cases that is rounding down.

Based on your example, you are rounding down the positive number but rounding up the negative number.
(Round Down being towards zero, Round Up being awary from zero)

This is clunky but might work for you.

=LET(Diff,B2-A2,
            posDiff,ABS(Diff),
            rndDown,posDiff-TIME(0,0,SECOND(posDiff)),
            rndUp,rndDown+TIME(0,SECOND(posDiff)<>0,0),
            IF(Diff>=0,
                 rndDown,
                  TEXT(rndUp,"-[h]:mm:ss")))

If you are successful in getting a negative time values to be recognised then you may be able to convert the swap out TEXT(rndUp,"-[h]:mm:ss") with -rndUp

=LET(Diff,B2-A2,
            posDiff,ABS(Diff),
            rndDown,posDiff-TIME(0,0,SECOND(posDiff)),
            rndUp,rndDown+TIME(0,SECOND(posDiff)<>0,0),
            IF(Diff>=0,
                 rndDown,
                -rndUp))

 

[Supernedz]

View attachment 104933

Formula being in B2 and copy down:

=ROUND(A2*24*60,0)/24/60

It doesn't matter the format it is in. Change it to your format and you'll get the same result.

Sorry, embarrassing but I’m still not getting it. In your example you are rounding up times over 30secs, I don’t want to do that, it must remove the seconds from the current minute. Also, are you saying I just can’t do it with negative values? There must be a way… thank you for your time by the way!

 

[Supernedz]

T

Based on your example, you are rounding down the positive number but rounding up the negative number.
(Round Down being towards zero, Round Up being awary from zero)

This is clunky but might work for you.

=LET(Diff,B2-A2,
            posDiff,ABS(Diff),
            rndDown,posDiff-TIME(0,0,SECOND(posDiff)),
            rndUp,rndDown+TIME(0,SECOND(posDiff)<>0,0),
            IF(Diff>=0,
                 rndDown,
                  TEXT(rndUp,"-[h]:mm:ss")))

If you are successful in getting a negative time values to be recognised then you may be able to convert the swap out TEXT(rndUp,"-[h]:mm:ss") with -rndUp

=LET(Diff,B2-A2,
            posDiff,ABS(Diff),
            rndDown,posDiff-TIME(0,0,SECOND(posDiff)),
            rndUp,rndDown+TIME(0,SECOND(posDiff)<>0,0),
            IF(Diff>=0,
                 rndDown,
                -rndUp))

Thanks, I don’t understand this formula but would like to try it. First of all, what values are you assuming I have for A2 and B2? I have two time values I want to sum (which I can do) and remove the seconds from (which I’m struggling with) but not to take one away from the other…