holiday calculator in Excel

[pendle666]

Hi

I've put together (with help from tek-tips,but I can't find my original post), a holidays calculator for new starters at my work. A "manual" calculation is 25 / 12 * 4 being the number of days entitlement, divided by the months in the year multiplied by the number of months in the holiday year the person will work. That bit is easy.

If the person starts mid month- then it's 25 / 12 / 23(the number of working days in October ) * 15 (the number of days worked in October if they start on the 13th. So the whole allowance worked out manually is 5.51.

The formula used in the spreadsheet is

=FLOOR((NETWORKDAYS($A5,EOMONTH($A5,0))/NETWORKDAYS($A5-DAY($A5)+1,EOMONTH($A5,0))+12-MONTH($A5))*25/12+0.4,0.5)

BUT, my line manager has asked that if the calculation is between, for example,

7.0 and 7.24, then the allowance should be 7.
between 7.25 and 7.74 then the allowance should be 7.5
between 7.75 and 8 then the allowance should be 8

Can anyone help me with this? I've tried changing the 0.4,0.5 bit at the end of the formula, but although it works for some calculations, it's not consistent.

Any advice gratefully received.

regards
Pendle

 

[Gavona]

Try: Double it, round the result to 0 dec places then halve it.
=Round(Ans*2,0)*2
Where Ans is the original answer.

Gavin

 

[SkipVought]

I thing that Gavin meant this...
[tt]
=ROUND(ans*2,0)/2
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Gavin

 

[pendle666]

Hello
I've tried this and whilst it works on some of the start dates, it's not consistent throughout - a bit like the original formula.

for example if someone starts work on 9th October 2008. Manually the calculation would be 25/12 * 2 = 4.16 (the entitlement for November and December). Then 25/12/23*17 = 1.53 (23 being the number of working days in October and 17 being the number of days the employee will work in that month). Plus the orignial 4.16 = 5.69.

But because the .69 is between .25 and .65, it should be rounded down to 5.5 days holiday, but my original formula and the suggested fix both give 6 days.

Am I asking the impossible here?

regards

Pendle

 

[GlennUK]

Hi Pendle,

the suggested fix of

=ROUND(ans*2,0)/2

gives 5.5 and not 6 as you say.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[pendle666]

Hi Glenn

I think I must be doing something wrong:

In Cell A13 is the date 09/10/2008 (9th October). The formula in cell B13 is:

=FLOOR((NETWORKDAYS($A13,EOMONTH($A13,0))/NETWORKDAYS($A13-DAY($A13)+1,EOMONTH($A13,0))+12-MONTH($A13))*25/12+0.4,0.5)

and the answer is 6.00

in cell C13 I've put the =ROUND(B13*2,0)/2 where B13 is the original answer and it comes back with 6 again.

regards

Pendle

 

[Gavona]

ROUND(5.69*2,0)/2 = 5.5

Gavin

 

[GlennUK]

Pendle,

If B13 returns 6.00 then of course C13 will also return 6.00 but you actually said the answer was 5.69 !?!?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

Here is a version of your formula that follows your logic ( I have not checked your logic ):

=ROUND(((NETWORKDAYS($A13,EOMONTH($A13,0))/NETWORKDAYS($A13-DAY($A13)+1,EOMONTH($A13,0))+12-MONTH($A13))*25/12)*2,0)/2

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[pendle666]

Hi

5.69 was the total if the calculation was done manually. It's sorted now with the code above.

regards

Pendle