# Holiday Allotments (1 Viewer)

#### Dreamweaver

##### Well-known member
This relates to the UK Holidays Gov.com To be honest Not sure If I'm overthinking things

I have worked out 2 ways of doing this and both give differrent values but i expected that but wanted to see

The varibles I'm useing are here
Code:
``````Dim Bnk As Boolean 'Does Company Pay Bank Holidays
Dim LstRun As Long 'Date AlLotments Last Run
Dim CPub As Integer 'Only expect 10-15 max
Dim StHo As Single, SCalc As Single ' The Statutory Holidays Allowence
Dim Cclo As Integer 'How Many company closures should be anywere near 20 a year
Dim Pref As DAO.Recordset, Emp As DAO.Recordset
Dim EStHo As Integer 'Make sure it's a whole number
Dim ECl As Integer 'Holds The Number Of Closures for a given Shift
Dim Wk As Integer, Ial As Single``````

This part of the code calculates the number of days to assign for a new employee who started say today 6th may 2020

On the gov site it calculates how much holiday a employee has accurecd like 28 ÷ 12 × 3

So I thought I would try altering that so my calc is like:

Code:
``````StHo = Pref("HolidayCalculation") 'Value from preferences table

StHo = (!DaysWorking * StHo)
Wk = Format(Date, "m")
Wk = 12 - Wk
EStHo = (StHo / 12) * Wk
'StHo=5.6 Which is used like 5x5.6 = 28 which is standard rate in UK``````

This give a value of 16.33333 but I want it to be rounded do use An integer for EStHo which gives me 16
my problem with this is it doesn't take into account the current month or maybe I'm being a dunce lol

So What I did Originally was work around the current week like:

Code:
``````                SCalc = StHo / 52 'Get Bass Rate For Year
Wk = Format(Date, "ww") 'What week are we on
Wk = 52 - Wk 'How many weeks are left in the year
Ial = Wk * SCalc 'Calc Number of days Ial Is A Integer As want whole number
'!DaysWorking gets it's value from the shifts System
EStHo = !DaysWorking * Ial 'Get Statutary Allowence``````

I have confired the value stored in preferences I.E. 5.6 and the Daysworking are stored in an employees record which is updated from the shifts system which is selected when an employee is added.

this function is for when an new employee is added or there shift pattern is altered.

my question is can anybody better at math than me "That will be most people" see if there is a better way or correct what I Have done

I do think I will have to except a value like 17.5 instead of rounded up to 18 which it gives at the moment

#### Dreamweaver

##### Well-known member
In the end I went for rounding down like If InStr(EStHo, ".") <> 0 Then EStHo = Left(EStHo, 2) Then way I get my whole number and The company don't loss money as I think it's one of them no win situations.

#### Dreamweaver

##### Well-known member
Changed my mind decided to give the admin the option of how to round it
Dim Ro As Integer, B As Integer 'Round Option From Preferences
Ro = Pref("RoundHolidays")
Code:
``````B = Left(Mid(EStHo, InStr(EStHo, ".") + 1, Len(EStHo)), 1)
Select Case Ro
Case 1 'Round Up
EStHo = Left(EStHo, InStr(EStHo, ".")) + 1
Case 2 'Round Down
EStHo = Left(EStHo, InStr(EStHo, "."))
Case 3 'System
If B < 6 Then
EStHo = Left(EStHo, InStr(EStHo, "."))
Debug.Print "Down"
Else
EStHo = Left(EStHo, InStr(EStHo, ".")) + 1
Debug.Print "Up"
End If
End Select
End If``````

#### Minty

##### AWF VIP
Am I missing something here Mick, why not use the round function, or the function provided here
to round how you want?

We used to calculate holiday entitlement by the week for start dates and allowed half days to be included.
So if the entitlement was 13.3 days we would give them 13.5

#### Dreamweaver

##### Well-known member
I Hate odd numbers Been working on this more The Round Function wont to what I want Or Don't think It will iF If I'm wrong please correct me.

his is what I now have It allows for half days do will have to alter a few objects from the employees example to use it.

Code:
`````` If InStr(EStHo, ".") <> 0 Then 'Not A While Number
B = Left(Mid(EStHo, InStr(EStHo, ".") + 1, Len(EStHo)), 1)
Select Case B
Case 5
EStHo = Left(EStHo, InStr(EStHo, ".")) + 0.5
Debug.Print "Half"
Case Is < 5
EStHo = Left(EStHo, InStr(EStHo, "."))
Debug.Print "Down"
Case Is > 5
EStHo = Left(EStHo, InStr(EStHo, ".")) + 1
Debug.Print "Up"
End Select
End If``````

#### Minty

##### AWF VIP
I've not looked closely at your code, but I personally wouldn't use string functions to evaluate numbers, different international separators could easily trip you up.
You can test for a fractional component by simply comparing the integer value;

`If Int(MyNumber) <> MyNumber then ... ' It's got a fraction component`

and just as easily the fractional portion is

`MyFraction = MyNumber - Int(MyNumber)`

Hope that helps a bit.
(I've been in the beer garden at home so am not going any deeper his evening!)

#### Dreamweaver

##### Well-known member
Thanks @Minty I'll look at doing it that way his will only ever be a number less the say 50 always thought the "." was a constant worldwide.

I haven't looked into how other countries calculate there holidays been working with the UK system just storing values the may be able to be used in other counties like out rate of 5.6.

thanks keep safe mick

#### Minty

##### AWF VIP
Thanks @Minty P.S. Adding that site to my links is it yours?
No - that 's a resource that's been around for quite some time, just appeared when I googled the round function, as I was having a mental blank about the difference between it and the Ceiling and Floor functions in SQL server.

Replies
14
Views
335
Replies
3
Views
155
Replies
11
Views
214
Replies
2
Views
122
Replies
3
Views
82