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!

 

[MARK858]

The part that excel can't show your negative times...

Not quite true, you can display negative time if using the 1904 date system. Just any dates currently using/created in the 1900 system will be incorrect. Not something I would recommend but it is there.

Book1
	A
3	13/01/2024 13:58
4	13/01/2024 10:58
5	-03:00:00
Sheet1

Cell Formulas
Range	Formula
A3	A3	=NOW()
A4	A4	=NOW()-TIMEVALUE("03:00:00")
A5	A5	=A4-A3

 

[Alex Blakenburg]

If you want to sum just change the B2-A2 to A2+B2.
The round down to the nearest minute is done by deducting the seconds from the time value.
The round up is taking the round down amount and if the seconds aren't = 0 add 1 minute. Since this is for the negative then multiplying by -1.

 

[Supernedz]

If you want to sum just change the B2-A2 to A2+B2.
The round down to the nearest minute is done by deducting the seconds from the time value.
The round up is taking the round down amount and if the seconds aren't = 0 add 1 minute. Since this is for the negative then multiplying by -1.

Hi, thanks for replying so quickly, I think I understand it better now and that does seem to have the desired effect but I have to duplicate across 50 sheets to prove it. Massive TIA if it has worked!!!

I do notice that the result does not contain 01/01/1904 any more and is only displaying hours and minutes. Even if I format the cells to show date and time the date is not there. Is that likely to be an issue for me?

 

[felixstraube]

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!

Ok sorry I misunderstood.

Is this what you want (i'm not showing the date part but it will work the same)?:

Then try this formula in B2 and copy down:

=LET(t_min, A2*24*60,
IF(t_min>=0,ROUNDDOWN(t_min,0),ROUNDUP(t_min,0))/24/60
)

 

[felixstraube]

Not quite true, you can display negative time if using the 1904 date system. Just any dates currently using/created in the 1900 system will be incorrect. Not something I would recommend but it is there.

Book1
	A
3	13/01/2024 13:58
4	13/01/2024 10:58
5	-03:00:00
Sheet1

Cell Formulas
Range	Formula
A3	A3	=NOW()
A4	A4	=NOW()-TIMEVALUE("03:00:00")
A5	A5	=A4-A3

Thanks. I didn't know that.

 

[Supernedz]

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))

This did it thanks! the second version of your formula with the swap out worked and kept the time format. Thank you so much! I don't know how you did it, I am in awe!

 

[Supernedz]

If you want to sum just change the B2-A2 to A2+B2.
The round down to the nearest minute is done by deducting the seconds from the time value.
The round up is taking the round down amount and if the seconds aren't = 0 add 1 minute. Since this is for the negative then multiplying by -1.

Thanks for your help, I think it's solved. You guys blow my mind!

 

[Alex Blakenburg]

Hi, thanks for replying so quickly, I think I understand it better now and that does seem to have the desired effect but I have to duplicate across 50 sheets to prove it. Massive TIA if it has worked!!!

I do notice that the result does not contain 01/01/1904 any more and is only displaying hours and minutes. Even if I format the cells to show date and time the date is not there. Is that likely to be an issue for me?

If you have changed your settings and are successfully showing negatives then the 2nd version I gave you should return a number representing date & time and formatting with the date included should show the date and time.

@felixstraube's latest suggestion is a more straightforward approach and I suggest you go with that.

 

[felixstraube]

After thorough testing I can say that this formula works as well and is a little simpler:

=INT(ROUND(A2*24*60,2))/24/60