Time on Job calculation

I knew it was a mistake to do it in a rush, Integers cannot be set to Null empty strings or Blank, I'll be back.

Brian
 
I've done that but whilst testing it I found a fault in that if the the dates give a year you get 11months and the number of days in the end month.

EG 2/march/2009 to 1/march/2010 gives 11 months 31 days. S**t

Brian
 
Ok sorted

Code: 
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 dayst - dayend = 1 Then GoTo completemonth
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

End If

completemonth:

If mthend >= mthst Then
nummonths = mthend - mthst
Numyears = yrend - yrst
Else
nummonths = 12 - mthst + mthend
Numyears = yrend - yrst - 1
End If

If numdays = 0 Then
fageyrmthday = ""
ElseIf numdays = 1 Then
fageyrmthday = numdays & " Day"
Else
fageyrmthday = numdays & " Days"
End If

If nummonths = 1 Then
fageyrmthday = nummonths & " Month " & fageyrmthday
ElseIf nummonths > 1 Then
fageyrmthday = nummonths & " Months " & fageyrmthday
End If


If Numyears = 1 Then
fageyrmthday = Numyears & " Year " & fageyrmthday
ElseIf Numyears > 1 Then
fageyrmthday = Numyears & " Years " & fageyrmthday
End If


End Function
 
Okay Brian,

I'm loving it! But, have two questions:

1) StartDate but no EndDate yet, I get #Error.

2) 3/1/10 til 3/31/10 gives me 31 days instead of 1 month. Is that correct?

Thanks!
 
Hey Brian,

Can I put something like: If dayend = "" Then Stop (no need to do the calculations)
 
As Startdate and Enddate are declared as dates in the function an immediate #error is returned if either is not. If you do not wish this to happen declare them as variants and add code like this as the first to be executed.

Code: 
If Not IsDate(enddate) Then
fageyrmthday = "No Enddate"
Exit Function
End If

brian
 
Brianwarnock said:
As Startdate and Enddate are declared as dates in the function an immediate #error is returned if either is not. If you do not wish this to happen declare them as variants and add code like this as the first to be executed.

Code: 
If Not IsDate(enddate) Then
fageyrmthday = "No Enddate"
Exit Function
End If

brian
Click to expand...

I keep getting the following:

Run-time error 94: Invalid use of Null
​
then it stops on the line: dayend = Day(EndDate)
 
I've no idea how that can happen with the code I posted, with the original code where Startdate and enddate are declared as Dates I get an immediate error in my query if they are not valid dates, with the later code I get No Enddate for example if the data passed is not a Date.

What is in the date field?

I'm sorry to say that i will be leaving shortly and will not be back on until tomorrow evening and then very briefly.

Brian
 
Ok You haven't replied and I have to go, you must have failed to declare them as dates, they are proper dates aren't they, do what I suggested a couple of posts back and include the If Not isnull etc code for both dates.

Brian

That should be If Not IsDate(...
 
Last edited:
Brianwarnock said:
I've no idea how that can happen with the code I posted, with the original code where Startdate and enddate are declared as Dates I get an immediate error in my query if they are not valid dates, with the later code I get No Enddate for example if the data passed is not a Date.

What is in the date field?

I'm sorry to say that i will be leaving shortly and will not be back on until tomorrow evening and then very briefly.

Brian
Click to expand...

Nothing in the date field--it is empty.
 
Brianwarnock said:
Ok You haven't replied and I have to go, you must have failed to declare them as dates, they are proper dates aren't they, do what I suggested a couple of posts back and include the If Not isnull etc code for both dates.

Brian
Click to expand...

Let me reread and retry it. Thanks!
 
The following function will return your string formatted as you want:

Code: 
Public Function TimeOnJob(dtStart As Date, dtEnd As Date) As String
Dim intNmbrOfMonths As Integer
intNmbrOfMonths = DateDiff("m", dtStart, dtEnd)
TimeOnJob = intNmbrOfMonths \ 12 & " year" & IIf(intNmbrOfMonths \ 12 > 1 Or intNmbrOfMonths \ 12 = 0, "s ", " ") & (intNmbrOfMonths Mod 12) & " month" & IIf(intNmbrOfMonths Mod 12 > 1 Or intNmbrOfMonths Mod 12 = 0, "s ", " ")
End Function

The function suggested above RETURNED NOTHING! That's why you got a blank line in the immediate window. (pay attention people!) With the function I've written, typing ? TimeOnJob(#3/12/2005#, #4/5/2010#) in the immediate window will return "5 years 1 month"
 
lmcc007 said:
Nothing in the date field--it is empty.
Click to expand...

Brian I think I found the problem. It is declared as:

Dim daystart As Integer
Dim dayend As Integer

daystart = Day(StartDate)
dayend = Day(EndDate)

I changed the above to Dim daystart and dim dayend, but now it still keeps coming back to this line:

It still keeps coming back to this line: dayend = Day(EndDate) because EndDate = Null​
 
Last edited:
This is the code I am running, modified now to allow for invalid dates within the code rather than just returning #error.

Code: 
Public Function fageyrmthday(startdate As Variant, enddate As Variant) 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

If Not IsDate(startdate) Then
fageyrmthday = "Invalid Startdate"
Exit Function
End If


If Not IsDate(enddate) Then
fageyrmthday = "Invalid Enddate"
Exit Function
End If

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

If dayst - dayend = 1 Then GoTo completemonth
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

End If

completemonth:

If mthend >= mthst Then
nummonths = mthend - mthst
Numyears = yrend - yrst
Else
nummonths = 12 - mthst + mthend
Numyears = yrend - yrst - 1
End If

If numdays = 0 Then
fageyrmthday = ""
ElseIf numdays = 1 Then
fageyrmthday = numdays & " Day"
Else
fageyrmthday = numdays & " Days"
End If

If nummonths = 1 Then
fageyrmthday = nummonths & " Month " & fageyrmthday
ElseIf nummonths > 1 Then
fageyrmthday = nummonths & " Months " & fageyrmthday
End If


If Numyears = 1 Then
fageyrmthday = Numyears & " Year " & fageyrmthday
ElseIf Numyears > 1 Then
fageyrmthday = Numyears & " Years " & fageyrmthday
End If


End Function

This is the result in my query


Code: 
id	startdate	enddate	TimeOnJob
1	02/01/2009	03/05/2009	4 Months 2 Days
2	02/02/2001	03/02/2009	8 Years 2 Days
3	29/12/2008	02/02/2010	1 Year 1 Month 5 Days
4	25/02/2009	04/03/2010	1 Year 8 Days
5	21/03/2009	24/03/2009	4 Days
6	02/03/2005	01/04/2008	3 Years 1 Month 
7	01/01/2010	             	Invalid Enddate
8		            01/02/2010	Invalid Startdate
 
c_smithwick said:
The following function will return your string formatted as you want:

Code: 
Public Function TimeOnJob(dtStart As Date, dtEnd As Date) As String
Dim intNmbrOfMonths As Integer
intNmbrOfMonths = DateDiff("m", dtStart, dtEnd)
TimeOnJob = intNmbrOfMonths \ 12 & " year" & IIf(intNmbrOfMonths \ 12 > 1 Or intNmbrOfMonths \ 12 = 0, "s ", " ") & (intNmbrOfMonths Mod 12) & " month" & IIf(intNmbrOfMonths Mod 12 > 1 Or intNmbrOfMonths Mod 12 = 0, "s ", " ")
End Function

The function suggested above RETURNED NOTHING! That's why you got a blank line in the immediate window. (pay attention people!) With the function I've written, typing ? TimeOnJob(#3/12/2005#, #4/5/2010#) in the immediate window will return "5 years 1 month"
Click to expand...

This must be one of the forums more arrogant posts.

He asks us to pay attention and ignores the fact that the poster wants days in the answer!!!

I am not sure which Function he is suggesting returns nothing , mine has worked since I first posted, I know because I tested, but I am very willing to see a neater solution.

Brian

FWIW here are his answers , EXPR1 next to mine, work it out yourself.
Code: 
id	startdate 	enddate	            TimeOnJob	        Expr1
1	02/01/2009	03/05/2009	4 Months 2 Days	        0 years 4 months 
2	02/02/2001	03/02/2009	8 Years 2 Days	        8 years 0 months 
3	29/12/2008	02/02/2010	1 Year 1 Month 5 Days	1 year 2 months 
4	25/02/2009	04/03/2010	1 Year 8 Days	       1 year 1 month 
5	21/03/2009	24/03/2009	4 Days	                     0 years 0 months 
6	02/03/2005	01/04/2008	3 Years 1 Month 	         3 years 1 month 
7	01/01/2010		Invalid Enddate	#Error
8		01/02/2010	Invalid Startdate	#Error
 
Last edited:
Thanks a lot Brian,

I just tested it and it works great! Loving it!

So, when you set your variables (that is, dayst = Day(startdate)) before entering the If Not IsDate Function, is that what is causing the error?
 
lmcc007 said:
Thanks a lot Brian,

I just tested it and it works great! Loving it!

So, when you set your variables (that is, dayst = Day(startdate)) before entering the If Not IsDate Function, is that what is causing the error?
Click to expand...

Before I added the If Not IsDate I had the Function like this

Public Function fageyrmthday(startdate As Date, enddate As Date) As String

This returned an #ERROR if either of the Dates was not a date, as per c_smithwick's in the example I posted.

I have now changed the Function to this

Public Function fageyrmthday(startdate As Variant, enddate As Variant) As String

and check the date in the code.

I never had the error you described unless I failed to declare the startdate and enddate as dates and then had a null date, I never did that except to prove to myself that would happen before responding yesterday.


Are you expecting Null enddates, perhaps where the project is ongoing?
If so you could set it to Date() and append "to Date" to the answer with a few simple changes.

Brian
 
Brianwarnock said:
Are you expecting Null enddates, perhaps where the project is ongoing?
If so you could set it to Date() and append "to Date" to the answer with a few simple changes.

Brian
Click to expand...

Now, you sort of lost me.

There may be some missing dates (start and end dates) until the records are found--and that's assuming we can find the missing information.
 
OK don't worry, I just wondered if an ongoing situation might have a startdate but no enddate, then the enddate could be set to the run date with Date() and the result formatted to say 3 Years 1 Month 2 Days "to Date"

Brian
 
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
497
spaLOGICng
S
Feral
Solved Finding Tenure Time
2
Replies
21
Views
884
Feral
Feral
E
Solved IIF function and dates
Replies
11
Views
1,720
eshai
E
B
Select from which date as a variable vba access
Replies
6
Views
353
Josef P.
J

Users who are viewing this thread

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