[X]My Assistant

[mike60smart]

Hi Everyone

In the attached spreadsheet on the Leave Planner Sheet -

Angelina Howe has an Entitlement of 26 Days
She has taken 25 days Leave (V) Plus 2 Half Days (H1 & H2)
Her remaining leave should be 0 but it shows .5 ?

Anyone have any idea why this would be happening?

Any help appreciated

 Copy_of_Copy_of_SJ_Planner_2019.zip ( 65.75K )Number of downloads: 5

[nvogel]

Some people might be worried that the attachment contains personal information. For the record, it might be a good idea for you to state that the names are not real and any identifiable personal information is removed. Just a thought.

This post has been edited by nvogel: Dec 4 2019, 09:29 AM

[cheekybuddha]

Hi Mike,

It's because that's what the formula in cell NW12 (and also all the other NW column formulae) specifies!

CODE

=IF(NJ12=NO12,"0",IF(NJ12>NO12,(NJ12-NO12)-0.5,FALSE))

NJ12 = 26
NO12 = 25

leads to:

CODE

=IF(26=25,"0",IF(26>25,(26-25)-0.5,FALSE))

So, you go to the False part of the first IF:
IF(26>25,(26-25)-0.5,FALSE)

Since 26>25 you go to the True part and return the value:
(26-25)-0.5
Result = 0.5

I reckon the formula needs some adjusting!

[cheekybuddha]

Have you checked Peter Hibbs' Holiday Planner db in the Code Archive?

[mike60smart]

Hi David

I just don't understand why it is not working for just this one employee?

How would I modify the formula?

[cheekybuddha]

It's the literal 0.5 that is causing it.

Change the formula in NW7 to:

CODE

=IF(NJ7 = NO7, "0", IF(NJ7 > NO7, (NJ7 - NO7) - (0.5*(NU7 + NT7)), FALSE))

and drag down.

You have your sheet set to not display zeroes. IF you want to see zeroes, then instead use:

CODE

=IF(NJ7 = NO7, "0", IF(NJ7 > NO7, IF((NJ7 - NO7) - (0.5*(NU7 + NT7)) = 0, "0", (NJ7 - NO7) - (0.5*(NU7 + NT7))), FALSE))

hth,

d

[mike60smart]

Hi David

As usual works a treat

Many thanks yet again

[cheekybuddha]