Calculating Difference between two Dates

leonparkinson · Feb 8th 2011

Hi,

I am trying to calculate the difference between to date/times in hours however i only want to count hours between 07:30 - 20:00 Monday - Friday and 08:00 - 14:00 on Saturdays with Sundays being excluded.

Column C will contain the date/ times an piece of work has been started in the following format 23/01/2011 09:00

Column D will contain the date/ times an piece of work has been completed in the same format

I am looking to implement a formula that will calculate the amount of time it has taken to complete the piece of work.

Any help would be greatly appreciated.

Thanks

Leon

pangolin · Feb 9th 2011

Re: Calculating Difference between two Dates

see if this User defined Function helps you

Code

Function Get_Hours(Start_Date As Date, End_Date As Date)
a = WorksheetFunction.RoundDown(Start_Date, 0)
b = WorksheetFunction.RoundDown(End_Date, 0)
t1 = Start_Date - a
t2 = End_Date - b
q = b - a - 1
For j = 1 To q
mydate = a + j
p = WorksheetFunction.Weekday(mydate, 2)
Select Case p
Case 1 To 5
dtime = 12.5
Case 6
dtime = 6
Case 7
dtime = 0
End Select
tottime = tottime + dtime
Next j
time1 = WorksheetFunction.RoundDown((1 - t1) * 24, 0)
time2 = WorksheetFunction.RoundDown(t2 * 24, 0)
tottime = tottime + time1 + time2
Get_Hours = tottime
End Function

Display More

Herbds7 · Feb 9th 2011

Re: Calculating Difference between two Dates

Excel 2010 (only) Tables
Calculate time between 2 dates/times
include only working hours, exclude Sunday,
Saturday has odd hours.
With formulas.
http://c3017412.cdn.cloudfiles…cecloud.com/02_09_11.xlsx
If you get *.zip, don't unzip, just rename *.xlsx

Calculating Difference between two Dates

Participate now!

Share