Portion of Month Query Formula

[Dkub]

My apologies if I'm posting this in the wrong section ... please let me know if I need to move it.

I'm trying to write a query that will look at a specific date [HireDate], and also look at the full-time amount of the employee [BaseFTE]. The formula should then tell me what the FTE equivalent is for the portion of the month that employee was employed.

here's what I have so far for an employee hired in November 2010; we're using full months (not working day months):

((30-DatePart("d",[HireDate])+1)/30)*[BaseFTE]

I think I'm close, this isn't working properly yet. Can anyone help please?

 

[Tigs81]

what is the baseFTE refering to eg the daily pay, monthly pay etc
eg if its daily then use
(30-DatePart("d",[HireDate])+1)*[BaseFTE]
if monthly
((30-DatePart("d",[HireDate])+1)/30)*[BaseFTE]

are you going to be entering in the hiredate for each employee every month? and change the query every month to match the number of days

 

[Brianwarnock]

To overcome Tigs81's very valid last point you can change the formula to

((Day(DateSerial(Year([hiredate]),Month([hiredate])+1,0)-Day([hiredate])+1))/Day(DateSerial(Year([hiredate]),Month([hiredate])+1,0)))*[basefte]

Point of info if you have never used Dateserial the last parameter, the Day , if 0 returns the last day of the previous Month.

Brian