Rounding Negative time to the nearest minute in Excel

[Demtro73]

Good Day Group.

I have searched the internet and this forum and have seen some questions and responses similar to my question but none that have an answer I can use, so I am hoping somebody can help.

Spreadsheet configuration -
I am using the 1904 date system so my spreadsheet will display negative time.
formatting for calculated time cells us h:mm
formatting for time cells is Time using 1:30 PM formatting, but the data in the cell has seconds, that are hidden.

I currently use MROUND to round to the nearest minute up or down
Example below

This works most of the time, however there are times when a negative number appears, and when I try to MROUND the negative number when adding it to a positive number I get the #NUM Error. I understand why I get this error.

I know that I probably need to use ROUND instead of MROUND, however all the similar issues I have found are for rounding to hours and not minutes.

I have tried to manipulate the ROUND formula to calculate rounding for seconds but cannot seem to figure it out. I created a table to show the data.

A1-E2 show it working properly.

A5-E5 shows the #NUM Error since MROUND does not work with negative times

I need the formulas in C5 and E5 to round up or down to the nearest minute when there are negative numbers in the calculated cells.

Any help is greatly appreciated. I am on a work computer and cannot install anything, otherwise i would have used the Mini-sheet feature.

	A	B	C	D	E
1	Schedule	Enter	A-B	Total Time	C+D
2	8:00:32 AM	7:45:00 AM	=MROUND(A2-B2,"0:01") result is 0:16	0:38	=MROUND(C2+D2,"0:01") result is 0:54
3
4	Schedule	Enter	A-B	Total Time	C+D
5	8:00:32 AM	8:07:00 AM	=MROUND(A5-B5,"0:01") result is #NUM	0:38	=MROUND(C5+D5,"0:01") result is #NUM

 

[Cubist]

Why not take the ABS of the subtraction to ensure it's positive?

 

[Demtro73]

Why not take the ABS of the subtraction to ensure it's positive?

Great Question...but I need to know if the number is positive or negative, and I need to use that number in a mathematical calculation farther down the road. I considered seeing if i could format the cells to identify the negative number as a red number similar to accounting, however that wont work when I have to take -0:15 and add 0:38 to get the final overall time from A-Z.

I do appreciate the feedback.

 

[Cubist]

Great Question...but I need to know if the number is positive or negative, and I need to use that number in a mathematical calculation farther down the road. I considered seeing if i could format the cells to identify the negative number as a red number similar to accounting, however that wont work when I have to take -0:15 and add 0:38 to get the final overall time from A-Z.

I do appreciate the feedback.

Formatting the cells doesn't resolve the #NUM error. If you need the negative calculation down the road then just do the subtraction again without the ABS. As for the purpose of column C, just take ABS()?

 

[Scott Huish]

Perhaps:

=-INT(-(A5-B5)*1440)/1440

Book2
	A	B	C
5	8:00:32 AM	8:07:00 AM	-0:06:00
6	8:00:32 AM	7:45:00 AM	0:16:00
Sheet1

Cell Formulas
Range	Formula
C5:C6	C5	=-INT(-(A5-B5)*1440)/1440