Holiday Allotments (1 Viewer)

Dreamweaver

Well-known member
Local time
Today, 15:47
Joined
Nov 28, 2005
Messages
2,466
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
Local time
Today, 15:47
Joined
Nov 28, 2005
Messages
2,466
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
Local time
Today, 15:47
Joined
Nov 28, 2005
Messages
2,466
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
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,355
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
Local time
Today, 15:47
Joined
Nov 28, 2005
Messages
2,466
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
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,355
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
Local time
Today, 15:47
Joined
Nov 28, 2005
Messages
2,466
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

P.S. Adding that site to my links is it yours?
 

Minty

AWF VIP
Local time
Today, 15:47
Joined
Jul 26, 2013
Messages
10,355
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.
 

Users who are viewing this thread

Top Bottom