Holiday entitlement formula

[frannyfen]

Hi,
I need to develop a formula to work out holiday entitlement from a given date (usually the start date) to the end of the year (our holiday year runs form 1st Jan to 31st December). I am not at all good at excel and only really understand very basic formulas. Holiday entitlement should be worked out as weeks remaining in the current year and rounded up to the nearest half day.


The headings will go in the following Cells:

A1 = Start Date
B1 = Annual holiday entitlement (this may not always be the same figure)
C1 = Current year's entitlement

The data for each new starter will follow on down each column.


Can anyone help me.
Thanks Fran

 

[oldbrewer]

dec 31 minus start date returns number of days employed let us say 200

entitlement is say 30 days so would be 30*200/365

for non new starter dec 31 minus jan 1 =364 so need to add 1

so the 200 needs to be 201

rounding - 17.7 +.5 = 18.2 int(18.2) = 18
17.4 +.5 = 17.9 int(17.9) =17

so you need to detect if fraction is greater than .5 before you integerise it - if it is then drop it back to 17.5

can you work out the formulas you need from that?

 

[frannyfen]

dec 31 minus start date returns number of days employed let us say 200

entitlement is say 30 days so would be 30*200/365

for non new starter dec 31 minus jan 1 =364 so need to add 1

so the 200 needs to be 201

rounding - 17.7 +.5 = 18.2 int(18.2) = 18
17.4 +.5 = 17.9 int(17.9) =17

so you need to detect if fraction is greater than .5 before you integerise it - if it is then drop it back to 17.5

can you work out the formulas you need from that?

Hi,
Unfortunately my knowledge of excel is really basic so I would need to know how to set up the date tabs and how to enter the dates and set up the formula cell. Sorry Im really an excel beginner although Im learning! If you can lead me through step by step it would be really great.

Thanks

 

[oldbrewer]

	D10
	01/01/2013	to	31/12/2013	holiday entitlements
	name	date started	annual holiday entitlement	started before 1/1/13?	entitlement
	bill	10/04/2013	30	no	21.780822
	fred	01/02/2011	30	yes	30
	tom	01/09/2013	25	no	8.2876712
	rounding - do you want 21.78 to be 21.5 or 22 ?
	started before 1/1/13 column
	first cell
	=IF(E14<$D$10,"yes","no")	the date 10/04/2013 is in cell E14
	to enter a date say April 10th 2013
	=10/4/2013

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[frannyfen]

Many thanks. Is this going by weeks or by days. Weeks would be best but this is looking like what I need and I will give it a go and see what happens. As far as rounding up the figures are concerned we would be rounding up to the nearest half day so the 2.1780822 will be 22 and 8.2876712 will be 8.5, thanks.

 

[oldbrewer]

22 22/7 = 3.14
int(3.14) = 3
3*7 = 21
22-21 = 1
3 weeks 1 day
or use 5 as divisor lol