Calculating Time (1 Viewer)

LadyDi

Registered User.
Local time
Today, 08:39
Joined
Mar 29, 2007
Messages
894
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.
 

Brianwarnock

Retired
Local time
Today, 15:39
Joined
Jun 2, 2003
Messages
12,701
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.

Brian
 

LadyDi

Registered User.
Local time
Today, 08:39
Joined
Mar 29, 2007
Messages
894
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?
 

Brianwarnock

Retired
Local time
Today, 15:39
Joined
Jun 2, 2003
Messages
12,701
This code produces the result below

Code:
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. :cool:


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

Brian
 
Last edited:

LadyDi

Registered User.
Local time
Today, 08:39
Joined
Mar 29, 2007
Messages
894
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.:cool:
 

Brianwarnock

Retired
Local time
Today, 15:39
Joined
Jun 2, 2003
Messages
12,701
It helps keep the old brain ticking along. I'm glad we got there.

Brian
 

LadyDi

Registered User.
Local time
Today, 08:39
Joined
Mar 29, 2007
Messages
894
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.
 

Brianwarnock

Retired
Local time
Today, 15:39
Joined
Jun 2, 2003
Messages
12,701
Wow this is a blast from the past.

I don't understand

As it stands right now, the minutes are displayed as 0.1 for one minute and 0.10 for ten minutes.

The function I wrote outputs a string such as

51 Hours 1 mins

it is simple to make that

51 Hours 01 mins

add
Dim varmins

then after the calculation
varmins = Format(mins, "00")

and replace mins with varmins in the output

functionname = hours & " Hours " & varmins & " mins"

But whether this helps you or not I don't know.


You could change the function to output

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.

Brian
 

LadyDi

Registered User.
Local time
Today, 08:39
Joined
Mar 29, 2007
Messages
894
That is perfect. I had actually already modified it to show the time as a decimal (hours.minutes). Thank you very much for your help.

I hope your wife's health improves.
 

Users who are viewing this thread

Top Bottom