Time on Job calculation

[raskew]

Hi -

To get full months you could use something like this:

NumMonths = DateDiff("m", startdate, enddate + (day(startdate) > day(enddate))

The + (day(startdate) > day(enddate)) portion is a Boolean statement which will yield -1 if True, 0 if False.

HTH - Bob

 

[lmcc007]

Hi -

To get full months you could use something like this:

NumMonths = DateDiff("m", startdate, enddate + (day(startdate) > day(enddate))

The + (day(startdate) > day(enddate)) portion is a Boolean statement which will yield -1 if True, 0 if False.

HTH - Bob

I don't get this code. I copied and pasted it but see no difference.

 

[lmcc007]

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

I still have not come up with anything on figuring this out because you have leap year, and the months with 31 days.

I'm sure if I new how to correctly ask the question I probably could find the answer on the web.

Anyway, thanks for your help.

 

[vbaInet]

I still have not come up with anything on figuring this out because you have leap year, and the months with 31 days.

I'm sure if I new how to correctly ask the question I probably could find the answer on the web.

Anyway, thanks for your help.

Does this mean some questions have been answered and some haven't? What haven't you found a solution for?

 

[lmcc007]

Does this mean some questions have been answered and some haven't? What haven't you found a solution for?

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

 

[Brianwarnock]

This requires a different approach, the code below assumes inclusive dates ie if the job is from 1/1/2010 2/1/2010 uk dates then it is 2 days, you can easily alter the code if this is not what you want.
I have not added the text as per your Switch nor checked for valid dates.

Brian

Public Function fageyrmthday(startdate As Date, enddate As Date) As String
Dim dayst As Integer
Dim dayend As Integer
Dim mthst As Integer
Dim mthend As Integer
Dim yrst As Integer
Dim yrend As Integer
Dim numdays as Integer
Dim nummonths as Integer
Dim Numyears as Integer

dayst = Day(startdate)
dayend = Day(enddate)
mthst = Month(startdate)
mthend = Month(enddate)
yrst = Year(startdate)
yrend = Year(enddate)


If dayend >= dayst Then
numdays = dayend - dayst + 1
Else
numdays = Day(DateSerial(yrst, mthst + 1, 0) - dayst + dayend + 1)
    If mthst < 12 Then
    mthst = mthst + 1
    Else
    mthst = 1
    yrst = yrst + 1
    End If

End If
If mthend >= mthst Then
nummonths = mthend - mthst
numyears = yrend - yrst
Else
nummonths = 12 - mthst + mthend
numyears = yrend - yrst - 1
End If
fageyrmthday = numyears & " " & nummonths & " " & numdays
End Function

 

[lmcc007]

Thanks Brian,

I knew someone has done this before especially if you are dealing with timecards, time reports, termination, human resources and so on.

Let me print it out, study it, and try it.

 

[Brianwarnock]

No, not done it before so please test thoroughly , I did a few tests but may have missed something.

Brian

 

[Brianwarnock]

The key is this bit so I have added a few comments.

If dayend >= dayst Then
numdays = dayend - dayst + 1
Else
numdays = Day(DateSerial(yrst, mthst + 1, 0) - dayst + dayend + 1) ' ie lastday of month-startday =endday+1 for inclusive day
    If mthst < 12 Then ' beceause we have covered startmonth in days
    mthst = mthst + 1  ' we need to adjust the month
    Else
    mthst = 1           ' and possibly the year
    yrst = yrst + 1
    End If

 

[vbaInet]

Thanks Brian,

I knew someone has done this before .....

He's just a genius lol

 

[lmcc007]

No, not done it before so please test thoroughly , I did a few tests but may have missed something.

Brian

The code is almost there—just a small detail missing. Singular and plural. How do I get it to work? Remember, before I was using Switch([days] = 1, [days] & " day", [days] < 30, [days] & " days") so it could like look like the following:

3 years, 1 day
11 months
1 year, 6 days
1 year, 3 months, 5 days
2 years, 1 month, 1 day

 

[vbaInet]

Use an IIF() function.

 

[lmcc007]

Use an IIF() function.

Yeah, I tried the below, but running into a problem when it is a zero:

YearMonthDay = IIf(numberyears = 1, numberyears & " year ", "") & IIf(numberyears > 1, numberyears & " years ", "") & _
IIf(numbermonths = 1, numbermonths & " month ", "") & IIf(numbermonths > 1, numbermonths & " months ", "") & _
IIf(numberdays = 1, numberdays & " day ", "") & IIf(numberdays > 1, numberdays & " numberdays ", "")

And the code looks messy.

I need to study the Select Case like Brian suggested above and then maybe I can add to this code.

 

[Brianwarnock]

This is untested
Change
fageyrmthday = numyears & " " & nummonths & " " & numdays
to
fageyrmthday = numyears & " strYears" & nummonths & " strMonths" & numdays & " strdays"
where strYears etc are declared as strings.

If NumYears=0 then
Numyears=""
StrYears=""
elseif
Numyears=1
strYears="Year"
else
strYear="Years"
end if

ditto for months and days

Brian

 

[lmcc007]

This is untested
Change
fageyrmthday = numyears & " " & nummonths & " " & numdays
to
fageyrmthday = numyears & " strYears" & nummonths & " strMonths" & numdays & " strdays"
where strYears etc are declared as strings.

If NumYears=0 then
Numyears=""
StrYears=""
elseif
Numyears=1
strYears="Year"
else
strYear="Years"
end if

ditto for months and days

Brian

I keep getting an error: Run-time error: 13, Type Mismatch...

 

[vbaInet]

It would help if you indicated what line the error is being thrown. When the error pops-up click the DEBUG button and tell us what line is being highlighted in yellow.

 

[lmcc007]

It would help if you indicated what line the error is being thrown. When the error pops-up click the DEBUG button and tell us what line is being highlighted in yellow.

Numyears = ""

 

[vbaInet]

Comment it out.

 

[lmcc007]

Comment it out.

I did and the code ran but ignored the other coding. That is:

If Numyears = 0 Then

Numyears = ""
strYears = ""​

elseif

Numyears = 1
strYears = "Year"​

Else

strYear = "Years"​

End If​

 

[vbaInet]

Brian would be able to talk through his code when he's next on.