I'm sorry. If an order was started at 7:15 and completed at 17:45, I would like it to show that it took 9 and a half hours to process (minus one hour for lunch). The extra hour and a half would be considered overtime.
Can you see how inconsistent this is with an 8 hour day, as I said a job from 7:15 one day to 8:15 the next is only 9 hours.
Thus I'm sorry to say that I don't think I can help any more, I just cannot figure how to calculate accurate times for the first and last days.
You did ask about excluding weekends and holidays. There is an ADDIN Networkdays which will handle this for you, be aware that it returns an inclusive number of days including the first and last ie Friday 4th April to Monday 7th is 2 days.
Install the addin and read the help
Google networkdays for info.
You are right, it is rather inconsistent. Do you think we could get it to work, if we based it on a 10 hour (11 hours including lunch) work day, instead of an 8 hour work day?
Function bjwtimefunc2(date1 As Date, date2 As Date, ndays As Long) As String
' ndays is value returned ftom worksheet function NETWORKDAYS
Dim days As Long, hours As Long, mins As Long, totalmins As Long
Dim minsfrstday As Long, minslstday As Long
If TimeValue(date1) > #6:00:00 PM# Then
date1 = DateValue(DateAdd("D", 1, date1)) + #7:00:00 AM#
ndays = ndays - 1
ElseIf TimeValue(date1) < #7:00:00 AM# Then
date1 = DateValue(date1) + #7:00:00 AM#
End If
If ndays < 3 Then ' ndays is inclusive
days = 0 ' we do not count first and last day
Else:
days = ndays - 2
End If
If DateValue(date1) = DateValue(date2) Then
mins = DateDiff("n", TimeValue(date1), TimeValue(date2))
If mins > 600 Then mins = mins - 60 'allow for lunch
Else
minsfrstday = DateDiff("n", TimeValue(date1), #6:00:00 PM#)
If minsfrstday > 600 Then minsfrstday = minsfrstday - 60 'allow for lunch
minslstday = DateDiff("n", #7:00:00 AM#, TimeValue(date2))
If minslstday > 600 Then minslstday = minslstday - 60 'allow for lunch
mins = minsfrstday + minslstday
End If
totalmins = days * 600 + mins
hours = Int(totalmins / 60)
mins = totalmins - hours * 60
bjwtimefunc2 = hours & " Hours " & mins & " mins"
End Function
I didn't attach the spreadsheet as I'm not sure what happens regarding addins
The function uses the result of the addin NETWORKDAYS from a column on the sheet, to exclude weekends and holidays.
Whole days count for 10 hours
the working day is 7 am till 6 pm
Times after 6 pm are moved to 7 am the following day
Times before 7 am are moved to 7 am same day.
All times per day > 10 hours have 1 hour deducted
sorry the dates are English like me. in the sample output
Oh! and trying to lay it out legibily (sp) is a nightmare.
If you see any problems don't be afraid to say, this is more exciting than answering simple question.
start end networkdays function result
01/03/2011 09:00 02/03/2011 08:00 2 10 Hours 0 mins
01/03/2011 09:00 03/03/2011 08:00 3 20 Hours 0 mins
01/03/2011 09:00 02/03/2011 11:00 2 13 Hours 0 mins loses 1 hr for lunch
01/03/2011 09:00 03/03/2011 11:00 3 23 Hours 0 mins loses 1 hr for lunch
02/03/2011 09:00 02/03/2011 11:00 1 2 Hours 0 mins
25/03/2011 15:36 28/03/2011 08:05 2 3 Hours 29 mins incl weekend
14/03/2011 19:41 15/03/2011 08:00 2 1 Hours 0 mins
12/04/2011 07:30 19/04/2011 18:00 6 59 Hours 30 mins incl weekend loses 2 hours for lunch, 1st and last day
12/04/2011 05:00 19/04/2011 08:00 6 51 Hours 0 mins incl weekend loses 1 hr for lunch
Thank you!! That works perfectly and gives us exactly what we needed. I really appreciate you taking the time to help me with this. I'm glad you enjoyed the challenge.
I just thought of another question regarding this code. Is there a way to modify it so that numbers between 1 and 9 will be diplayed with two digits (01 and 09)? As it stands right now, the minutes are displayed as 0.1 for one minute and 0.10 for ten minutes. In order for my final caclulations to run correctly, I have been changing the 0.1 to 0.01. I was just wondering if there is a way to automate that.
51.01
where the digits before the decimal point represent the hours and those after the minutes.
BTW my personal circumstances have changed and I am now on here less often and can spare less time as I am now my wife's carer, but I will attempt to help if I can.