Solved Calculate days based on start dates

[oxicottin]

How could I write one query formula for these guidelines below?

Jan 1 - April 30 = 6 Days
May 1 - Aug 31 = 4 Days
Sep 1 - Nov 30 = 2 Days
Dec 1 - Dec 31 = 0 days

Scenarios

If I have an employee who started and their [EmpDateOfHire] is 5/2/22 so they would get 4 paid days off the first year and every following year after
they get 6 paid days.

Or a second scenario, I have an employee who started and their [EmpDateOfHire] is 2/2/22 so they would get 6 paid days off the first year and every following year after
they get 6 paid days.

Or third scenario, I have an employee who started and their [EmpDateOfHire] is 10/1/22 so they would get 2 paid days off the first year and every following year after
they get 6 paid days.

 

[arnelgp]

you just apply the "prorata".
for each complete year (of employment) an employee got 6 paid day offs.
therefore 0.5 for each month (for 12 months = 6 paid day offs).
you don't give anything when an employee is less than 2 months of employment.

=number of months * 0.5

this is consistent with military/government.

 

[oxicottin]

@arnelgp the first calendar year of employment this scenario is for, there is no scenario after the first calendar year because they imminently get 6 days starting the first of every year after.

Example:

If I have an employee who started and their [EmpDateOfHire] is 5/2/22 so they would get 4 paid days off the remainder of 2022 then once 2023 hits they automatically get 6 days then once 2024 they get 6 days........

I would think it would be kinda like:

FlexDaysAquired: IIf([EmpDateOfHire]>=#1/1/2022# And ([EmpDateOfHire]>=#4/30/2022#,0,6)

BUT the above formula I would need to compare the #1/1/2022# and the #4/30/2022# to the year that the [EmpDateOfHire] is in to determine the correct number and I don't know how.

 

[arnelgp]

If I have an employee who started and their [EmpDateOfHire] is 5/2/22 so they would get 4 paid days

you need to confirm this?
what if your employed at 5/2/2022, give him 4 days off with pay, on jun/2022 (1 month) he goes AWOL?
i don't think any employer will do that. he must, at least, complete his Probationary period.

 

[oxicottin]

@arnelgp yep its right.... Here is another example that I know is incorrect BUT I have no idea how to write it...

FlexDaysAquired: IIf([EmpDateOfHire]>=#1/1/DatePart("yyyy",[EmpDateOfHire]# And ([EmpDateOfHire]>=#4/30/DatePart("yyyy",[EmpDateOfHire],0,6)

 

[Solo712]

Function FlexDays (EmployeeDateofHire as Long) as Long
If Year(Date) > Year(EmployeeDateOfHire) then
    FlexDays = 6
Else
   Select Month(EmployeeDateofHire)
    Case  < 5
       FlexDays = 6
    Case  < 9
       FlexDays = 4
    Case  < 12
       FlexDays = 2
    Case Else
       FlexDays = 0
    End Select

 

[oxicottin]

@Solo712 I see.... Using a function looks to be better than a bunch of formulas in a query and works great thank you!

 

[Solo712]

@Solo712 I see.... Using a function looks to be better than a bunch of formulas in a query and works great thank you!

You are welcome.

 

[arnelgp]

what a Nice company, you join between jan and end of Apr, you automatically get 6 day off With Pay.

 

[Gasman]

@Solo712 I see.... Using a function looks to be better than a bunch of formulas in a query and works great thank you!

Always