Sum to work out annual leave

[Elmobram22]

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]

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]

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]

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

 

[namliam]

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]

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

 

[Elmobram22]

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

 

[Elmobram22]

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

 

[namliam]

This isnt vba, its simple inline functions

what days do you want to count?

 

[Elmobram22]

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]

something like:

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

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

 

[Elmobram22]

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]

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

 

[namliam]

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]

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]

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

 

[Elmobram22]

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]

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]

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

 

[namliam]

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