Time on Job calculation

L

lmcc007

Registered User.
Local time
Today, 14:42
Joined
Nov 10, 2007
Messages
635
I am trying to get the number of years and days spent on a job automatically. I tried the following:

Time on Job: DateDiff("m",[StartDate],[EndDate])

but it just give me the total months. Instead of saying 37 months, I want it to say 3 years and 1 month.

Is it possible?
 
Try a little creative use of the Int() function and the Mod() function.
 
pbaldy said:
Try a little creative use of the Int() function and the Mod() function.
Click to expand...

I'm not understanding what you mean. Well, let me look up what Int() and Mod() mean and do.
 
Hi -

Here's an example you can play with:

Code: 
startdate = #3/12/05#
enddate = #4/5/10#

x = datediff("m", startdate, enddate) 
? x
 61 

y = x\12 & " year" & iif(x\12>1 or x\12=0, "s ", " ") & (x mod 12) & " month" & iif(x mod 12>1 or x mod 12=0, "s ", " ")   
? y
5 years 1 month

HTH - Bob
 
Last edited:
Try to do like this,

DateDiff("m",[StartDate],[EndDate])/365
 
Hi Unicon -

Try to do like this,

DateDiff("m",[StartDate],[EndDate])/365
Click to expand...

Did you test this out. Using the same sample dates from
my previous post, it'd look like this:

Code: 
startdate = #3/12/05#
enddate = #4/5/10#

? datediff("m", startdate, enddate)/365
 0.167123287671233

What is that supposed to accomplish?

Puzzled - Bob
 
raskew said:
Hi -

Here's an example you can play with:

Code: 
startdate = #3/12/05#
enddate = #4/5/10#

x = datediff("m", startdate, enddate) 
? x
 61 

y = x\12 & " year" & iif(x\12>1 or x\12=0, "s ", " ") & (x mod 12) & " month" & iif(x mod 12>1 or x mod 12=0, "s ", " ")   
? y
5 years 1 month

HTH - Bob
Click to expand...

Hello,

I am trying the above code, but I get nothing. I type the following in the Code window:

Function TOJ()

Dim StartDate As Date
Dim EndDate As Date
Dim x


StartDate = #3/12/2005#
EndDate = #4/5/2010#

x = DateDiff("m", StartDate, EndDate)

End Function
​

When I go to the Immediate Window and enter ? x, it returns a blank line.

Am I missing something?

Thanks!
 
pbaldy said:
Try a little creative use of the Int() function and the Mod() function.
Click to expand...


years = int((DateDiff("m",[StartDate],[EndDate])) / 12)
or
years = DateDiff("y",[StartDate],[EndDate])

months = DateDiff("m",[StartDate],[EndDate]) - years*12
 
smig said:
years = int((DateDiff("m",[StartDate],[EndDate])) / 12)
or
years = DateDiff("y",[StartDate],[EndDate])

months = DateDiff("m",[StartDate],[EndDate]) - years*12
Click to expand...

Thanks Smig,

The above works!!
 
break it down

months = DateDiff("m",[StartDate],[EndDate])

SO

years = months \ 12 (\ is integer division - scraps the remanider)
months = months mod 12 (return the number of left over months)
 
smig said:
years = int((DateDiff("m",[StartDate],[EndDate])) / 12)
or
years = DateDiff("y",[StartDate],[EndDate])

months = DateDiff("m",[StartDate],[EndDate]) - years*12
Click to expand...

Smig,

I used a Switch Function to get it to display the way I want it to. In my query I entered the following expression:

Time on Job: Switch([years]=0,"",[years]=1,[years] & " year"+" ",[years]>1,[years] & " years"+" ") & Switch([months]=0,"",[months]=1,[months] & " month",[months]>1,[months] & " months")

Did I do this correct?

I tried using an iff function, but I had trouble with 3 comparison--that is, if years equal 0 then nothing, if years = 1 then "year", and if years > 1 then "years"
 
you can go with an iif inside iif

my advise for a complex calculation to create your oun function and send to it the startDate and endDate values.
it will be much easier for you to foloow exactly what go on.
 
smig said:
you can go with an iif inside iif

my advise for a complex calculation to create your oun function and send to it the startDate and endDate values.
it will be much easier for you to foloow exactly what go on.
Click to expand...

Okay Smig,

I created a Module 1 and entered the following:

Function TimeOnJob(years As Integer, months As Integer)
TimeOnJob = Switch([years] = 0, "", [years] = 1, [years] & " year" + " ", [years] > 1, [years] & " years" + " ") _
& Switch([months] = 0, "", [months] = 1, [months] & " month", [months] > 1, [months] & " months")
End Function
​

In my query I created the [years] and [months] expression. I created my form using the query; therefore, I have two text boxes: years and months.

How do I reference this function in my query or form?

I'm new--still trying to learn VBA and communicate better--I'm a slow learner :)
 
in your query where you normaly put the name f the field (from table) you put your function:
TimeOnJob([years], [months])
where Years and Months are the name of the fields.

but as you don't have these and they are also calculated why won't you make the function to also calculate them:

Code: 
Function TimeOnJob(StartDate As date, EndDate As date) as string
 
dim years as long
dim months as long
 
[I]years = int((DateDiff("m",StartDate,EndDate)) / 12)
months = DateDiff("m",StartDate,EndDate) - years*12[/I]

TimeOnJob = Switch(years = 0, "", years = 1, years & " year" + " ", years > 1, years & " years" + " ") _
& Switch(months = 0, "", months = 1, months & " month", months > 1, months & " months")

End Function

in your query you put:
TimeOnJob([StartDate], [EndDate])

I think you can make your function more readable
some won't agree with me but I prefer longer code that it's easier to see what the intention was
Code: 
TimeOnJob = ""
 
select case year
  case 0
    TimeOnJob = ""
  case 1
    TimeOnJob =  iif(months > 0, "1 year and ", "1 year")
  case else
    TimeOnJob =  iif(months > 0, years & " years and ", years & " years")
end select
 
select case months
  case 0
    TimeOnJob = TimeOnJob
  case 1
    TimeOnJob = TimeOnJob & "1 month"
  case else
    TimeOnJob = TimeOnJob & months & " months"
end select
 
smig said:
in your query where you normaly put the name f the field (from table) you put your function:
TimeOnJob([years], [months])
where Years and Months are the name of the fields.

but as you don't have these and they are also calculated why won't you make the function to also calculate them:

Code: 
Function TimeOnJob(StartDate As date, EndDate As date) as string
 
dim years as long
dim months as long
 
[I]years = int((DateDiff("m",StartDate,EndDate)) / 12)
months = DateDiff("m",StartDate,EndDate) - years*12[/I]

TimeOnJob = Switch(years = 0, "", years = 1, years & " year" + " ", years > 1, years & " years" + " ") _
& Switch(months = 0, "", months = 1, months & " month", months > 1, months & " months")

End Function

in your query you put:
TimeOnJob([StartDate], [EndDate])

I think you can make your function more readable
some won't agree with me but I prefer longer code that it's easier to see what the intention was
Code: 
TimeOnJob = ""
 
select case year
  case 0
    TimeOnJob = ""
  case 1
    TimeOnJob =  iif(months > 0, "1 year and ", "1 year")
  case else
    TimeOnJob =  iif(months > 0, years & " years and ", years & " years")
end select
 
select case months
  case 0
    TimeOnJob = TimeOnJob
  case 1
    TimeOnJob = TimeOnJob & "1 month"
  case else
    TimeOnJob = TimeOnJob & months & " months"
end select
Click to expand...

I agree your code looks readable. Let me take a lunch break and comb back then I may understand it better because I have been trying this for a while and my mind is off.

I will try it in about an hour and let you know.

Thanks!
 
smig said:
in your query where you normaly put the name f the field (from table) you put your function:
TimeOnJob([years], [months])
where Years and Months are the name of the fields.

but as you don't have these and they are also calculated why won't you make the function to also calculate them:

Code: 
Function TimeOnJob(StartDate As date, EndDate As date) as string
 
dim years as long
dim months as long
 
[I]years = int((DateDiff("m",StartDate,EndDate)) / 12)
months = DateDiff("m",StartDate,EndDate) - years*12[/I]

TimeOnJob = Switch(years = 0, "", years = 1, years & " year" + " ", years > 1, years & " years" + " ") _
& Switch(months = 0, "", months = 1, months & " month", months > 1, months & " months")

End Function

in your query you put:
TimeOnJob([StartDate], [EndDate])

I think you can make your function more readable
some won't agree with me but I prefer longer code that it's easier to see what the intention was
Code: 
TimeOnJob = ""
 
select case year
  case 0
    TimeOnJob = ""
  case 1
    TimeOnJob =  iif(months > 0, "1 year and ", "1 year")
  case else
    TimeOnJob =  iif(months > 0, years & " years and ", years & " years")
end select
 
select case months
  case 0
    TimeOnJob = TimeOnJob
  case 1
    TimeOnJob = TimeOnJob & "1 month"
  case else
    TimeOnJob = TimeOnJob & months & " months"
end select
Click to expand...

Hi Smig,

It's almost there, not quite. Problem is that sometimes the employment only lasted 2 weeks; therefore, I need to say something like: 10 days, 21 days, and so on. I tried if less than 30 put the number of days, but there is February and some months go to the 31st.

Days: DateDiff("d",[StartDate],[EndDate]) works, it gives me total number of days.

I need it broken down. For instance, 3/1/10 to 4/5/10 is 0 years, 1 month, and 5 days.

What am I missing?

Thanks for your help!
 
I suggest you first try with times longer then a month to make sure it works for you.
as for days I'm not sure there is an easy solution. normaly I will take months as 30 days, but as you said yourself it's not very acurate.

you can of course check what was the month of startDate and the month of endDate and calculate the exact days. but this will make your function much longer and complicate :D
 
You must log in or register to reply here.

Similar threads

A
Filter Records
Replies
1
Views
390
June7
June7
S
extremely slow when exporting PDF
Replies
8
Views
495
spaLOGICng
S
Feral
Solved Finding Tenure Time
2
Replies
21
Views
884
Feral
Feral
E
Solved IIF function and dates
Replies
11
Views
1,713
eshai
E
B
Select from which date as a variable vba access
Replies
6
Views
351
Josef P.
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom