PDA

View Full Version : Calculate hours within timeframe



hullu
12-06-2012, 03:35 AM
Hi everybody!

I have 4 cells in excel where the user is supposed to enter
StartDate
StartTime
StopDate
StopTime

Now I would like to have a script that sums only the hours between these times, that are later than 09:00 PM and earlier than 06:00AM. This timewindow is actually different for workingdays and weekends.

The code below calculates the time in between - ignoring the two timewindows.

Sub HNS_Click()

Dim T1, T3, DiffDate As Double
Dim T2, T4, DiffTime, DiffTotal As String

'Fetch values from Excelsheet
T1 = DateValue(Range("A4")) 'StartDate
T2 = CStr(Range("B4")) 'StartTime
T3 = DateValue(Range("C4")) 'StopDate
T4 = CStr(Range("D4")) 'StopTime

'Calculations
DiffDate = T3 - T1 'Difference between days
DiffTime = (T4 - T2) 'Difference between hours
DiffTotal = (DiffDate * 24) + DiffTime 'Amout of hours between start and stop



Can you advise me how to find out how many others are in the timeframe?

Thank you very much!
Hullu