Years of service, minus inactive.

[CKonash]

Hello.

 

I have a few databases for fire departments and in a Personnel Table I have a "Date of Hire" field. I then uses a calculation to get their Years of service and display it as Years, Months, Days.

My question is: some members take a leave of absence or go inactive.

I'm not sure how to approach subtracting a leave of absence start and end date or set a field with a date they they went inactive and then their Years of service would calculate up until that date and not the current date.

 

Thank you for your time.

 

Chris.

 

 

Sent from my iPad using Tapatalk

[comment]

If someone went inactive, you would enter the date into an Inactivated field and use a calculation of =

If ( Inactivated ; Inactivated ; Get ( CurrentDate ) )

instead of Get ( CurrentDate ) in your existing calculation.

With leaves of absence it gets more complicated, because at least in theory a person may take more than one. So you would have to add them all up first and subtract the result from the duration. I don't know how you translate the duration into years, month and days - or why you do that at all - so I am not sure what method to suggest for this. I have written several times about the meaninglessness of expressing a duration in these terms - so I guess it doesn't really matter how exactly you do it. Perhaps just subtract the total number of days from the end date (as calculated above) before doing your thing.

 

[CKonash]

Thank you very much for your input.  I'll try that new calculation for when a member goes 'inactive'. 

As for the leave of absence.  Maybe I should just build into the Personnel Records table maybe 5 Start and 5 End dates for a person to take a leave of absence.  I know it isn't very clean and neat but I could add up the days of leave and then subtract them from the date of hire calculation.  

Here is the calculation field I have been using so far and how it is displayed based on the Get Current Date calculation.  

Thanks,

Chris

 

[comment]

6 hours ago, CKonash said:

 Maybe I should just build into the Personnel Records table maybe 5 Start and 5 End dates for a person to take a leave of absence.  I know it isn't very clean

No, it isn't. It would be better to have a related table for the leaves. Or perhaps you should have a related table for the periods of hire (this is assuming these leaves of absences are actually gaps between different periods of hire).

 

6 hours ago, CKonash said:

add up the days of leave and then subtract them from the date of hire

You would have to either add them to the date of hire or subtract them from the end date. And since the result is totally divorced from any actual date, you might consider simplifying your calculation to use average year and average month lengths.