Sum to work out annual leave (1 Viewer)

Elmobram22 · Mar 6, 2015

Hi all,

I have a database with shifts in for staff. They have a bunch of times in and times out over a four week period. I have gotten an average weekly amount of hours for each staff member based on this but I need another equasion to work out their leave entitlement. It breaks down like this...

Average weekly hours x 5.6 x number of days working in this period (ie start date and end of financial year day count) divided by number of days in the financial year (ie 1/4/2015 - 31/3/2016 day count)

I'm just wondering of a way to do the day count based on me keying in the start date of the staff member (default 1/4/2015) and that access can work out the days in that financial year left and the actual days in that financial year.

It sounds simple enough but I want to get it to automate based on my start date.

Cheers,

Paul

Elmobram22 · Mar 6, 2015

Also is there a way of getting the default date to track based on the financial year we are in. So for example when we are in 2018 it will show 01/04/2017 as the default start date?

namliam · Mar 6, 2015

This will shift the date by 3 months, i.e. make 1/4 be 1/1
dateadd("M", -3, date())

Then making that a year and adding 1/4 to it will make it doable
Dateserial(Year(dateadd("M", -3, date())), 4,1)

Add that as "where clause" to use a default start date

Kinda lost on your original question though

Elmobram22 · Mar 6, 2015

Will that give a day count for the financial year or will it see how many days are in an actual year?

namliam · Mar 6, 2015

It will fetch the days worked for the fiscal year ....
if today is anywhere from 1/4/2014 - 31/03/2015, it should return 1/4/2014 as a start date..

From there if you count them, should return the number of worked days.

Elmobram22 · Mar 6, 2015

Sorry got it now. Thank you so that gives me the default start date and the count.

Elmobram22 · Mar 6, 2015

How do you get it to count the days. I'm falling short. Was trying the...
DateDiff("d", CDate([Default Start Date), dat)

Elmobram22 · Mar 6, 2015

As you can tell I'm very very new to VBA but am learning it slowly

namliam · Mar 6, 2015

This isnt vba, its simple inline functions

what days do you want to count?

Elmobram22 · Mar 6, 2015

Errrrr yeah thats what I meant lol

Number of days in the year. So for example where you've set the start date up the amount of days until the end date (again default 31/3/whatever year)

Cheers

namliam · Mar 6, 2015

something like:

Code:

DaysInFY: Dateserial(Year(dateadd("M", -3, date()))+1, 4,1) - Dateserial(Year(dateadd("M", -3, date())), 4,1)

That what you are looking for (should return 365 / 366 obviously

)

Edit:

or probably better

Code:

DaysInFY: Dateserial(Year(dateadd("M", -3, date()))+1, 4,1) - Dateserial(Year(dateadd("M", -3, date())), 4,0)
[B][/B]

Elmobram22 · Mar 6, 2015

Right I'm stuggling to understand what this inline function is doing but I'm impressed with it! What I want to do next is have the option for a variable. So for example if I am working out the leave for a staff member who started after the 1st of april it can work out the days in the year from that point. Also if someone changes their hours that I can put in an end date for their current hours which works as a start date for their new set of hours. So for example:

John does a weekly average of 8.75 hours so his leave for the whole year would be...
Weekly Average x 5.6 x worked days divided by days in FY
8.75 x 5.6 x (366/366) = 49 hours

But if John does the same but starts on the 14th July the equasion changes to...

8.75 x 5.6 x (260/366) = 34.808743 hours

Elmobram22 · Mar 6, 2015

And 8.75 x 5.6 x (106/366) = 14.191257 hours

namliam · Mar 6, 2015

it basicaly calculates the difference of 03/31/2015 - 04/01/2014

The number of contract hours shouldnt be a parameter, instead should be maintained within the database... Same goes for contract start/end dates.

Elmobram22 · Mar 6, 2015

Yeah it is. I have a query doing that

Sum([Overall Hours]/4) The hours are maintained by a module that tracks the times in and out and returns the total hours minus the deductions.

Elmobram22 · Mar 6, 2015

Sorry bet you wish you'd never replied to this thread lol

Elmobram22 · Mar 6, 2015

In regards to the start end dates I just want them to be variable. Then the database will maintain the rest. So if someone leaves before the end of the year I can put that end date in and it'll tell me the amount of holiday entitlement they should have had before leaving or changing their hours.

namliam · Mar 6, 2015

Well on average there is a difference between "contract hours" and "worked hours"

Also contract start and end dates are properties that should be retained inside the database and not entered as a parameter.

I believe you need to step back and rethink your design all together before going forward again....

Elmobram22 · Mar 6, 2015

So maybe add contract yes/no each time record
And start end dates to be added in the staff table?

namliam · Mar 6, 2015

Wouldnt think so, staff will be staff, regardless of their contract period.

You would have a contract table where 1 employee would have 1 or more contracts, either parallel or in sequence indicating its validity and number of hours and potentially payments

Sum to work out annual leave (1 Viewer)

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

Registered User.

Registered User.

The Mailman - AWF VIP

Registered User.

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread