Displaying negative times correctly and using negative times in calculations

BlissC

New Member
Joined
Aug 28, 2017
Messages
47
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,606
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
47
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,468
Messages
5,764,509
Members
425,219
Latest member
datdanigg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top