Negative Values Not Showing as Negatives

[psycoperl]

I am working on a time tracking spreadsheet. If a Leave balance goes over the available leave minutes the Balance should show has negatives. While my formula works if the Days (D) or Hours (H) are negatives... the minutes (M) does not show the values as negatives, it shows the absolute value. How can I fix this?

Column G shows the Leave Minutes Balance (this is normally a hidden column) and Columns H shows the values in a human readable format of Days, Hours and Minutes.

Leave_Use_Plan_FY2021.xlsx
	D	E	F	G	H
1	D	H	M	LEAVE MINUTES	CT BAL
2	6	0	45	2565	CT BAL: 06D 00H 45M
3	0	-3	-30	2355	CT BAL: 05D 04H 15M
4	0	-3	-30	2145	CT BAL: 05D 00H 45M
5	0	-7	0	1725	CT BAL: 04D 00H 45M
6	0	-5	0	1425	CT BAL: 03D 02H 45M
7	0	-4	0	1185	CT BAL: 02D 05H 45M
8	0	8	0	1665	CT BAL: 03D 06H 45M
9	0	-7	0	1245	CT BAL: 02D 06H 45M
10	0	-1	0	1185	CT BAL: 02D 05H 45M
11	0	1	30	1275	CT BAL: 03D 00H 15M
12	0	3	30	1485	CT BAL: 03D 03H 45M
13	0	-7	0	1065	CT BAL: 02D 03H 45M
14	0	-4	-30	795	CT BAL: 01D 06H 15M
15	0	-7	0	375	CT BAL: 00D 06H 15M
16	0	5	0	675	CT BAL: 01D 04H 15M
17	0	4	0	915	CT BAL: 02D 01H 15M
18	0	10	0	1515	CT BAL: 03D 04H 15M
19	0	6	30	1905	CT BAL: 04D 03H 45M
20	0	0	45	1950	CT BAL: 04D 04H 30M
21	0	-1	-30	1860	CT BAL: 04D 03H 00M
22	0	-1	-45	1755	CT BAL: 04D 01H 15M
23	0	-1	0	1695	CT BAL: 04D 00H 15M
24	0	-3	0	1515	CT BAL: 03D 04H 15M
25	0	-4	-45	1230	CT BAL: 02D 06H 30M
26	0	0	-45	1185	CT BAL: 02D 05H 45M
27	0	-7	0	765	CT BAL: 01D 05H 45M
28	0	-3	-30	555	CT BAL: 01D 02H 15M
29	0	-4	0	315	CT BAL: 00D 05H 15M
30	0	-1	0	255	CT BAL: 00D 04H 15M
31	-1			-165	CT BAL: 00D -02H 15M
Sheet1

 

[Rick Rothstein]

Might help to show us your formula and also to indicate which columns you are showing us.

 

[Peter_SSs]

Might help to show us your formula and also to indicate which columns you are showing us.

Hi Rick

Can't you see the columns at the top of the mini-sheet?

.. and hover over or click one of the formula cells to get the formula?

 

[AlphaFrog]

This will calculate the minutes with a negative sign
TEXT(SIGN(G31)*MOD(G31,60),"00")

=CONCATENATE(B31, " BAL: ", TEXT(ROUNDDOWN((G31/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((G31-ROUNDDOWN((G31/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(SIGN(G31)*MOD(G31,60),"00"),"M")

 

[Rick Rothstein]

Hi Rick

Can't you see the columns at the top of the mini-sheet?

Apparently I inadvertently scrolled the mini-sheet without realizing it and only saw Row 1 downward?
It never occurred to me that this was an XL2BB sheet and to try and scroll it.

SIDE NOTE: Is there anyway to get our old set of emojis back? The new set is not anywhere near as apt even with all of the in new set (the vast majority of which have no use for us in this forum).

 

[psycoperl]

This will calculate the minutes with a negative sign
TEXT(SIGN(G31)*MOD(G31,60),"00")

=CONCATENATE(B31, " BAL: ", TEXT(ROUNDDOWN((G31/(60*7)),0),"00"),"D ",TEXT(ROUNDDOWN((G31-ROUNDDOWN((G31/(60*7)),0)*60*7)/(60),0),"00"),"H ",TEXT(SIGN(G31)*MOD(G31,60),"00"),"M")

Thanks... I needed up with a new formula for another purpose. but this looks great. Thank you.

 

[Peter_SSs]

SIDE NOTE: Is there anyway to get our old set of emojis back?

I suspect not, but you could ask in the About This Board forum if you want.

 

[Rick Rothstein]

I suspect not, but you could ask in the About This Board forum if you want.

Thanks for the tip (I wasn't sure what sub-forum would be appropriate)... I just posted a message there.