Length of service.

[jd]

Hello there

I want to calculate length of service. Excel sort of guesses and doesn't do
it.

I DON'T WANT THE DIFFERENCE IN DAYS.

eg: start 01/may/1980 leave 31/may/2008 = 28 years and 31 days.
also 31/may/80 to 01/may/08 gives 27 years and 335 days? because of 29 feb.

then i need a simple conversion to decimal 31/365 and added to the 28 years.

so i want 5 fields/cells

name start end service
actual

john 01/may1980 31/may/2008 28/031 28.084

with only a few lines this seems to be a spreadsheet problem, and i only
want to get the correct answer in access or excel.

so any suggestions please.

also i do not work well in the abstract, so an absolutely simple example or
explanation is best suited.

thanks in advance for any help.

regards

jd

 

[Douglas J. Steele]

If all you want is the service in years, use the Age calculations given in
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web".
Essentially,

DateDiff("yyyy", [DateHired], Date()) - IIf(Format(Date(), "mmdd") <
Format([DateHired], "mmdd"), 1, 0)

If you want the service in years, months and days, see
http://www.accessmvp.com/DJSteele/Diff2Dates.html

 

[jd]

Hiya

thankyou for a quick response.

unfortunately i don't have a clue as to what the answer actually means.

i can read the syntax, but do not know where it goes in the database.

sorry if this has wasted your time.

i have spent a couple of hours trying to get excel to do it and i cant.

 

[Douglas J. Steele]

Sorry, I totally missed the fact that you're trying to do this in Excel.

This newsgroup is for questions about Access, the database product that's
part of Office Professional. You'd be best off reposting your question to a
newsgroup related to Excel.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hiya

thankyou for a quick response.

unfortunately i don't have a clue as to what the answer actually means.

i can read the syntax, but do not know where it goes in the database.

sorry if this has wasted your time.

i have spent a couple of hours trying to get excel to do it and i cant.

If all you want is the service in years, use the Age calculations given
in
http://www.mvps.org/access/datetime/date0001.htm at "The Access Web".
Essentially,

DateDiff("yyyy", [DateHired], Date()) - IIf(Format(Date(), "mmdd") <
Format([DateHired], "mmdd"), 1, 0)

If you want the service in years, months and days, see
http://www.accessmvp.com/DJSteele/Diff2Dates.html

 

[jd]

Hiya

Thanks for the attempt at help.

however what i want is a solution.

excel didnt work.
access hasnt worked so far.

next choice is to give up.

after that

install a floppy drive and use some old disks and re-install a borland C
programme for dos and try and remember how to use that.

after that use a manual method, calendar wheel, pencil and paper.
this method works.

i hoped for a fluid solution that dynamically changed if i changed a leaving
date.

oh well. i could try Lotus 123 that has a few different functions to excel.

 

[Wayne-I-M]

Thats a little harsh.

Thanks for the attempt at help.

however what i want is a solution.

Douglas was just trying to help

I don't have access on this machine but it would seem to be quite simple.
If it were me:
I would use Douglas's formula to get the Years and months of service
Next (another column) get the number of days from the end of the previous /
(before leaving) month until that date they left (or today if they are still
there). You will need to use the Datedd function to get te month before they
left (or today)

Then format the answer by adding (concencate) the 2 columns together. So
you would have #Years # Months # Days

I would also add to it (it's just how I am) by using the IIf the employee
leaving date is null then give a prompt or, by default, use Date()

The answers can be formated as text (for word export) or a numbers (for
excel export), or text and numbers for excel formulas.

Don't forget that Douglas does not get paid to your work for you. He is
(like everyone else one this forum) juust trying to help (unpaid) simply
becuase he wants to.

Good luck

 

[Steve Sanford]

Thats a little harsh.

I agree.

JD, everyone that posts a question wants an answer. On your part, you didn't
give a very good explaination of your problem - just a demand. If you don't
understand the reply, ask more questions. That is why you are posting at this
forum.

And don't be so quick to give up.

This is a fairly easy in Access. You can use a custom function:

'-----------------------
Public Function Service(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2), Month(Date2) + 1, 0)) + D + 1
End If
Service = Y & " years " & M & " months " & D & " days"
End Function
'-----------------------

But is sounds like you are more comfortable in EXCEL.

If cell B5 = 5/1/1980 (start) and cell C5 = 5/31/2008 (end), then the
formula in cell D5 (Service) should be:

(all on one line)
=DATEDIF(B5,C5,"y") & "/" & DATEDIF(B5,C5,"yd")+(MONTH(DATE(YEAR(C5), 2,
29))=2)

and in cell E5 (Actual) would be:

(all on one line)
=DATEDIF(B5,C5,"y") + (DATEDIF(B5,C5,"yd")+(MONTH(DATE(YEAR(C5), 2,
29))=2))/365


HTH