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.

In column N, which shows the time remaining in

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:

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

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):
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: