Displaying negative times correctly and using negative times in calculations

BlissC

New Member
Joined
Aug 28, 2017
Messages
41
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
DEGIJKLMNO
28First date bookedLast date bookedHalf dayNumber of days bookedAdditional/ other hoursTotal time bookedType of leaveAnnual leave remaining
29HoursDays
3003/10/201919/10/2019No644:24Annual#140:3619 days 00:00
3103/12/201905/12/2019No214:48Annual#125:4817 days 00:00
3203/03/202005/03/2020Other12:009:24Annual#116:2415 days 05:24
3306/06/202008/06/2020Yes0.53:42Annual#112:4215 days 01:42
3424/08/202024/08/2020Other02:002:00Annual#110:4214 days 07:06
3528/08/202002/09/2020No214:48Flexi0110:4214 days 07:06
3607/09/202007/09/2020Yes0.53:42Annual#107:0014 days 03:24
3717/09/202023/09/2020No322:12Annual#84:4811 days 03:24
3812/10/202012/10/2020Yes0.53:42Annual#81:0610 days 07:06
3918/10/202026/10/2020No429:36Annual#51:306 days 07:06
4001/01/202105/01/2021No214:48Annual#36:424 days 07:06
4108/02/202108/02/2021Yes0.53:42Annual#33:004 days 03:24
4203/04/202108/04/2021No214:48Annual#18:122 days 03:24
4312/05/202118/05/2021No322:12Annual#-04:00#VALUE!
4419/05/202119/05/2021No17:24Annual# #VALUE!
450 0  
460 0  
470 0  
Leave
Cell Formulas
RangeFormula
I30:I47I30=SUM(F30-H30)
M30:M47M30=IF(ISNUMBER(SEARCH("Annual",L30)),K30,"0")
N30N30=IFERROR(IF(OR(ISBLANK(D30),ISBLANK(E30)),"",IF(J26-M30<0, "-" & TEXT(ABS(J26-M30),"hh:mm"), J26-M30)),"")
O30:O47O30=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:N47N31=IFERROR(IF(OR(ISBLANK(D31),ISBLANK(E31)),"",IF(N30-M31<0, "-" & TEXT(ABS(N30-M31),"hh:mm"), N30-M31)),"")
K30:K47K30=IF(OR(ISBLANK(D30),ISBLANK(E30)),"",(I30*$R$15)+J30)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I30:I71Expression=ISBLANK(G30)textNO
I30:I71Cell Valuecontains "ERROR"textNO
L30:L47Cell Valuecontains "ERROR"textNO
A30:C58,J30:K58,M30:XFD58,A29:E29,I29:XFD29,A59:E71,G59:G71,J59:XFD71,1:28,72:1048576,N32:N71Cell Valuecontains ""textNO
A30:C58,J30:K58,M30:XFD58,A29:E29,I29:XFD29,A59:E71,G59:G71,J59:XFD71,1:28,72:1048576,N32:N71Cell Valuecontains "ERROR"textNO
M30:M71Celldoes not contain a blank value textNO
D59:E71,J30:K58,M30:O58,G59:G71,J59:O71,N32:N71Expression=$L30="Cancelled"textNO
O30:O71Expression=$N30<0textNO
N30:N73Cell Value<0textNO
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:N71Cell Value<0textNO
Cells with Data Validation
CellAllowCriteria
L30:L47List=leave_type
G30:G47ListYes,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):

Code:
=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:

Code:
=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
 
Last edited:

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,488
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 28, 2017
Messages
41
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,973
Members
412,430
Latest member
Huuktkt
Top