add time to start date based on working hours

[karthik82.vk]

I have a startdate with time and enddate with time for each project. the enddate will be calculated based on the startdate + 8 hrs for each project.

My working hours starts from 09:00:00 and ends by 18:00:00 in the evening

If the enddate falls after my working time, then the off work time needs to be neglected.

say for example if my startdate is 11/08/2012 16:58:18 then my enddate will be calculated as 11/9/2012 12:58:18. ie 8 hrs will get added from the start date.

but i want the end date to be displaying only the working hours and not off work hours.

the output that i need is something like

time my work hour ends on - timevalue of my starttime = hours worked for that day + time remaining on total work hrs (8) which needs to get added on next day working hours.

18:00:00 - 16:58:18 = 1:01:42

so I have worked for 1:01:42 hrs on a project for that day in the total 8 hrs allocated for me

I have 06:58:18 left in my total working hours on that project and this left out time must be added to the nextday starting work time

09:00:00 + 06:58:18 = 15:58:18

ie my enddate must be displayed as 11/09/2012 15:58:18

I have tried a lot in doing this but i am not able to do so
can anyone help me in this.

thanks in advance...
karthik venkatraman

[mohanvijay]

did you try "Datediff" and "Dateadd" functions?

[karthik82.vk]

Hi,

I tried but I am not able to do it..

[mohanvijay]

Can you post a sample file with result that you want

[msofficems]

you still luking for this 2b resolved

[msofficems]

I have a sample code if you want on which you can build your requirement

[karthik82.vk]

Hi,

Yes I am looking for this code still....

I got a sample code through msdn forum. But since i am developing the application in vb.net too i am getting an error when i execute the code.


Sub test1() Dim dtStart As Date dtStart = #11/8/2012 4:58:18 PM# Debug.Print getEndTime(dtStart) ' 15:38 next day dtStart = #11/8/2012 9:30:00 AM# Debug.Print getEndTime(dtStart) ' 17:30 same day End Sub Function getEndTime(startTime As Date) As Date Dim dt As Date dt = startTime + TimeSerial(8, 0, 0) If dt > Int(startTime) + TimeSerial(18, 0, 0) Then dt = startTime + TimeSerial(6 + 9 + 8, 0, 0) End If getEndTime = dt End Function


it will be highly helpful for me it you provide me the code and if its also works on vb.net then it will be great..

[msofficems]

I have the code in excel VBA c if it works:




Sub two()
Dim duration As Variant: Dim startdate As Variant: Dim enddate As Variant

duration = TimeSerial(8, 0, 0): 'MsgBox "Duration : " & duration
Range("b4").Value = duration

Dim s As String: s = InputBox("Enter date and time in dd-mm-yyyy hh:mm:ss format", "Datetime", "1-1-2012 9:0:0")
startdate = CDate(s): MsgBox startdate: MsgBox "start : " & Format(startdate, "mm/dd/yyyy hh:nn:ss")
Range("b5").Value = Format(startdate, "mm/dd/yyyy hh:nn:ss")

Dim dt As Long: dt = CLng(WorksheetFunction.RoundDown(startdate, 0)): 'MsgBox "date : " & Format(dt, "mm/dd/yyyy hh:nn:ss")

If startdate > (dt + TimeSerial(18, 0, 0)) Then
exhausted = 0
enddate = (dt + 1) + TimeSerial(9, 0, 0) + duration
ElseIf (startdate + duration) > (dt + TimeSerial(18, 0, 0)) Then
exhausted = dt + TimeSerial(18, 0, 0) - startdate
enddate = (dt + 1) + TimeSerial(9, 0, 0) + (duration - exhausted)
ElseIf startdate <= (dt + TimeSerial(10, 0, 0)) Then
exhausted = TimeSerial(8, 0, 0)
enddate = startdate + duration
End If

MsgBox "EndDate : " & enddate
End Sub


reply with the results