Hello,
I am looking for a formula to calculate total working hours based off date and time and excludes the weekends. I have attached a spreadsheet that I will be applying the formula to. See below for data information.
Start date: 8/3/16 9:00 am
End date: 8/16/16 12:00 pm
Start time: 6:00 am
End time: 6:00 pm
We work Mon-Thur so Fri, Sat, Sun would be our weekend.
I am looking to get total hours scheduled based on the information above.
Hi adaniel.
There doesn't seem to be an attachment.
cheers
Here you go.
Hi Daniel,
The problem is a complex one, there is no simple formula.
You can see how complex things can be here: Schedule Calculator
I suggest using a User Defined Function, there are a few versions available on web. I tested one for you:
Dim rangeH, numH, rangeD, numD, startD, durW, durD, durH, durM, startW, endW, remTime As Long
Dim startH, endDate As Double
rangeH = 8 ' Starting hour of working day
numH = 12 ' Length of working day in hours
rangeD = 2 ' Starting day of working week
numD = 4 ' Length of working week in days
' Calculates offset from 00:00 Monday in starting week
startW = Fix(StartTime) - DatePart("w", StartTime)
startD = DatePart("w", StartTime) - rangeD
startH = (StartTime - Fix(StartTime)) * 24
' Calculates end time in working weeks, hours, minutes
remTime = Minutes + (startD * numH * 60) + ((startH - rangeH) * 60)
durW = Fix(remTime / 60 / numH / numD)
remTime = remTime - (durW * numD * numH * 60)
durD = Fix(remTime / 60 / numH)
remTime = remTime - durD * 60 * numH
durH = Fix(remTime / 60)
remTime = remTime - durH * 60
durM = remTime
' Converts working weeks into calendar weeks
endDate = startW + durW * 7 + rangeD + durD + (rangeH + durH) / 24 + durM / 1440
EndDayTimeM = endDate
End Function
Change the parameters in red as desired (it's set to 12 hours per day, starting from Monday, 4 days per week, each day starts at 8 AM)
The solution comes from: calculating-end-times-with-working-hours-and-skipping-weekends
The function expects minutes for duration, this is the reason I divided your estimated hours by 60, in cell G17 (where I tested the function)
If the work schedule is more complex, with different work hours per day, different start hours, these static parameters can be changed to variables, adding them to function arguments.
Thank you!
I am going to work on this and I will let you know how it goes. I will let people know about your website and look into purchasing.
Thanks again.
