Query Date Help W/Avg work days

[aldunford]

please note that in order to get the date 6/28/1979 I am using average work
days each month of 21.75

I have tried to Add just the total days to the date but it's not correct
because I need it to be based off the 21.75 days per month not actually days.

Total Years of Prior Service Days Hire Date Adjusted Hire Date

7 years 4 months 6 days 2685 6/15/1987 2/7/1980 (s/b 2/9/80)
1 year 8 months 13 days 620 2/10/2005 6/1/2003 (s/b 5/27/03)

This is what is being calculated so far once I run the query.

 

[aldunford]

in terms it would be a total of 261 work days in a year ALWAYS even in leap
years

 

[John W. Vinson]

please note that in order to get the date 6/28/1979 I am using average work
days each month of 21.75

Since no month contains 21.75 workdays, and since that is an APPROXIMATION,
you will inevitably be off by at least a day or two.

Perhaps you could post your existing query and show how you would do the same
calculation manually.

 

[John Spencer]

So a month (on average) has 365.25/12 days.
A month has (on average per your statement) 21.75 days.

So every work day represents 1.3994253 month days.

Every month day represents 0.71457905 work days.

Beyond giving you the ratios I have no idea to calculate what you want.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[aldunford]

Let me approach this in a different way. I used the below DateDiff Function
to return an answer as 7years 4 months 19 days

If I can seperate those into their own columns I could then make the
calculations using my 21.75 average work days.

I appreciate all your help thanks so much!!

 

[aldunford]

If I was to do this manually:
Org. Hire Date 6/18/1979 & Resign Date 11/7/1986


1986 11 (-1) 7 (Add 30) *always add 30 days again a Avg.
1979 6 18

7 4 19


Then I take the 7 years 4months 19 days and subtract from the New Hire Date

11/17/1986
Year Month Days
1986 11 (-1) 17 (add 30 days only if taking a month)
7 4 19

1979 6 28

Basically if I can take the 7years 4months 19days and subtract that from a
date 11/17/1986 I would get my answer. It's okay if it's one or two days off

 

[John Spencer]

Why don't you just subtract days and not worry about work days?

DateDiff("d", [Org Hire Date],[Resign Date]) will give you the total number
of days in the period

DateAdd("d",-DateDiff("d",[Org Hire Date],[Resign Date]),[New Hire Date])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[aldunford]

I have already done that however my employer's work days in a year is always
261 or 1957.5 hours in a year. So I need the service years & hours to be
accurate.

Let me approach this in a different way. I used the below DateDiff Function
to return an answer as 7years 4 months 19 days

If I can seperate those into their own columns I could then make the
calculations using my 21.75 average work days.

Any ideas how to seperate them? I can get the years but not the months and
days. It converts it to a total months of 88 I only want it to give me 4
months.


This is a very complicated payroll system so it's not easy!!
I appreciate all your help thanks so much!!

Why don't you just subtract days and not worry about work days?

DateDiff("d", [Org Hire Date],[Resign Date]) will give you the total number
of days in the period

DateAdd("d",-DateDiff("d",[Org Hire Date],[Resign Date]),[New Hire Date])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If I was to do this manually:
Org. Hire Date 6/18/1979 & Resign Date 11/7/1986


1986 11 (-1) 7 (Add 30) *always add 30 days again a Avg.
1979 6 18

7 4 19


Then I take the 7 years 4months 19 days and subtract from the New Hire
Date

11/17/1986
Year Month Days
1986 11 (-1) 17 (add 30 days only if taking a month)
7 4 19

1979 6 28

Basically if I can take the 7years 4months 19days and subtract that from
a
date 11/17/1986 I would get my answer. It's okay if it's one or two days
off