Displaying negative times correctly and using negative times in calculations

[BlissC]

I'm working on a leave card at present, where employees can request annual leave, but I'm having difficulty in displaying results of the calculation of leave remaining where an employee has exceeded their leave allowance. In previous versions of the leave card I've simply switched to the 1904 date system to get around the problem, but to get other elements of the card to work properly, I've had to go back to the standard 1900 format. This means I've got '######' displaying wherever there's a negative time result from the calculation of leave remaining - leave requested, and also affects other calculations on the card that use the result.

There are two columns that show the amount of leave remaining, one which shows the amount in 'hh:mm', and the other that shows it in 'days hh:mm'. Below is the part of the leave card that calculates and displays time requested and time remaining.

Electronic_leave-card_v1-4_working_copy.xlsm
	D	E		G		I	J	K	L	M	N	O
28	First date booked	Last date booked		Half day		Number of days booked	Additional/ other hours	Total time booked	Type of leave		Annual leave remaining
29											Hours	Days
30	03/10/2019	19/10/2019		No		6		44:24	Annual	#	140:36	19 days 00:00
31	03/12/2019	05/12/2019		No		2		14:48	Annual	#	125:48	17 days 00:00
32	03/03/2020	05/03/2020		Other		1	2:00	9:24	Annual	#	116:24	15 days 05:24
33	06/06/2020	08/06/2020		Yes		0.5		3:42	Annual	#	112:42	15 days 01:42
34	24/08/2020	24/08/2020		Other		0	2:00	2:00	Annual	#	110:42	14 days 07:06
35	28/08/2020	02/09/2020		No		2		14:48	Flexi	0	110:42	14 days 07:06
36	07/09/2020	07/09/2020		Yes		0.5		3:42	Annual	#	107:00	14 days 03:24
37	17/09/2020	23/09/2020		No		3		22:12	Annual	#	84:48	11 days 03:24
38	12/10/2020	12/10/2020		Yes		0.5		3:42	Annual	#	81:06	10 days 07:06
39	18/10/2020	26/10/2020		No		4		29:36	Annual	#	51:30	6 days 07:06
40	01/01/2021	05/01/2021		No		2		14:48	Annual	#	36:42	4 days 07:06
41	08/02/2021	08/02/2021		Yes		0.5		3:42	Annual	#	33:00	4 days 03:24
42	03/04/2021	08/04/2021		No		2		14:48	Annual	#	18:12	2 days 03:24
43	12/05/2021	18/05/2021		No		3		22:12	Annual	#	-04:00	#VALUE!
44	19/05/2021	19/05/2021		No		1		7:24	Annual	#		#VALUE!
45						0				0
46						0				0
47						0				0
Leave

Cell Formulas
Range	Formula
I30:I47	I30	=SUM(F30-H30)
M30:M47	M30	=IF(ISNUMBER(SEARCH("Annual",L30)),K30,"0")
N30	N30	=IFERROR(IF(OR(ISBLANK(D30),ISBLANK(E30)),"",IF(J26-M30<0, "-" & TEXT(ABS(J26-M30),"hh:mm"), J26-M30)),"")
O30:O47	O30	=IFERROR(IF(OR(ISBLANK(D30),ISBLANK(E30)),"",INT(MROUND($N30/R$15,1/86400))&" days "&TEXT(MAX(0,$N30-INT(MROUND($N30/R$15,1/86400))*R$15),"hh:mm")),"-"&INT(MROUND(ABS($N30)/R$15,1/86400))&" days "&TEXT(MAX(0,ABS($N30)-INT(MROUND(ABS($N30)/R$15,1/86400))*R$15),"hh:mm"))
N31:N47	N31	=IFERROR(IF(OR(ISBLANK(D31),ISBLANK(E31)),"",IF(N30-M31<0, "-" & TEXT(ABS(N30-M31),"hh:mm"), N30-M31)),"")
K30:K47	K30	=IF(OR(ISBLANK(D30),ISBLANK(E30)),"",(I30*$R$15)+J30)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I30:I71	Expression	=ISBLANK(G30)	text	NO
I30:I71	Cell Value	contains "ERROR"	text	NO
L30:L47	Cell Value	contains "ERROR"	text	NO
A30:C58,J30:K58,M30:XFD58,A29:E29,I29:XFD29,A59:E71,G59:G71,J59:XFD71,1:28,72:1048576,N32:N71	Cell Value	contains ""	text	NO
A30:C58,J30:K58,M30:XFD58,A29:E29,I29:XFD29,A59:E71,G59:G71,J59:XFD71,1:28,72:1048576,N32:N71	Cell Value	contains "ERROR"	text	NO
M30:M71	Cell	does not contain a blank value	text	NO
D59:E71,J30:K58,M30:O58,G59:G71,J59:O71,N32:N71	Expression	=$L30="Cancelled"	text	NO
O30:O71	Expression	=$N30<0	text	NO
N30:N73	Cell Value	<0	text	NO
M29:XFD30,T25:XFD26,O19:P19,P18:S18,T17:XFD20,A59:E71,M31:M71,O31:XFD71,A29:C58,J30:K58,M31:N58,G59:G71,J59:N71,72:1048576,27:28,A25:N25,A26:M26,1:16,21:24,A19:M19,A17:S17,A18:N18,A20:Q20,N32:N71	Cell Value	<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L30:L47	List	=leave_type
G30:G47	List	Yes,No,Other

In column N, which shows the time remaining in hh:mm I've tried using this formula to display the negative times correctly (this is the one currently shown above):

=IFERROR(IF(OR(ISBLANK(D31),ISBLANK(E31)),"",IF(N30-M31<0, "-" & TEXT(ABS(N30-M31),"hh:mm"), N30-M31)),"")

This does correctly show a negative value (at least on one row) for the time remaining in hh:mm, but as it's text , isn't then able to be used in the other calculations I need to use it in.

This was the original formula in column N:

=IFERROR(IF(OR(ISBLANK(D31),ISBLANK(E31)),"",N30-M31),"")

Similarly, I also need the time remaining in 'days hh:mm' to display as a negative time (i.e. "-1 day 02:48) where the employee's exceeded their leave allowance, but be able to use it in other calculations too. The existing formula in this column is already rather complex, which was necessary to fix some persistent rounding errors in this column in previous versions of the leave card.

I know there are various ways to get Excel to display a time with a '-' sign, but altering the cells to text results etc. doesn't work because I need to be able to use the results in column N in the formula in column O, and in other places. Currently, using the formula on column N that displays the result of the calculation as text, it breaks formulas in other places on the leave card that include the result of column N, e.g. a statement telling the employee how much leave they have remaining as a percentage of their total leave allowance.

Could anyone advise me please how I can get both the columns N (hh:mm) and O (days hh:mm) to display negative values where necessary but still allow me to use the results elsewhere?

Many thanks,

Bliss

 

[jasonb75]

For calculations you need your original formula formatted as general. If you want to display the times as negative then you will need to do that with the text formula in a separate column.

 

[BlissC]

Thanks, that's working great. I've put the original formula in a hidden column, and I'm displaying the times in columns N and O with the text formula, as before, but using the hidden column for calculations.