workday include saturday
 

Hi
I want to calculate the end date and time of a project (duration: "x"
hours).

Working days are from Monday 6:00 to Saturday 14:00.

Monday: from 6:00 to 24:00
Tuesday: from 0:00 to 24:00
Wednesday: from 0:00 to 24:00
Thursday: from 0:00 to 24:00
Friday: from 0:00 to 24:00
Saturday: from 0:00 to 14:00

I want to calculate it using the start date and time anytime between
Mon and Sat.

Any suggestions?

stremetzky

workday include saturday
 

=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(start_date)& ":"&INT(end_date))))={2,3,4,5,6,7})*({18,24,24,24, 24,14}))-
(HOUR(start_date)-CHOOSE(WEEKDAY(start_date),0,6,0,0,0,0,0))-(CHOOSE(WEEKDAY(start_date),24,24,24,24,24,24,14)-HOUR(end_date))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"stremetzky" wrote in message
oups.com...
Hi
I want to calculate the end date and time of a project (duration: "x"
hours).

Working days are from Monday 6:00 to Saturday 14:00.

Monday: from 6:00 to 24:00
Tuesday: from 0:00 to 24:00
Wednesday: from 0:00 to 24:00
Thursday: from 0:00 to 24:00
Friday: from 0:00 to 24:00
Saturday: from 0:00 to 14:00

I want to calculate it using the start date and time anytime between
Mon and Sat.

Any suggestions?

stremetzky

workday include saturday
 

On Apr 11, 1:43 pm, "Bob Phillips" wrote:
=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(INT(start_date)& ":"&INT(end_date))))={2,3*,4,5,6,7})*({18,24,24,24 ,24,14}))-
(HOUR(start_date)-CHOOSE(WEEKDAY(start_date),0,6,0,0,0,0,0))-(CHOOSE(WEEKDA*Y(start_date),24,24,24,24,24,24,14)-HOUR(end_date))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"stremetzky" wrote in message

oups.com...



Hi
I want to calculate the end date and time of a project (duration: "x"
hours).

Working days are from Monday 6:00 to Saturday 14:00.

Monday: from 6:00 to 24:00
Tuesday: from 0:00 to 24:00
Wednesday: from 0:00 to 24:00
Thursday: from 0:00 to 24:00
Friday: from 0:00 to 24:00
Saturday: from 0:00 to 14:00

I want to calculate it using the start date and time anytime between
Mon and Sat.

Any suggestions?

stremetzky- Hide quoted text -

- Show quoted text -

Thank you for help. I've tried your formula but I have a problem:
I'd like to calulate the "end date&time" of a project from the
"start_date&time" and the duration (hours) of a project.
In the above formula it is supposed I already know the "end_date"...
??

workday include saturday
 

Hello,

I suggest to use my UDF:

Function count_hours(dt1 As Date, dt2 As Date, _
vwh As Variant) As Date
'Returns time between dt1 and dt2 but counts only
'hours given in table vwh: for example
'04:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'09:00 23:30
'00:00 00:00
'This table defines hours to count for each day
'of the week (starting with Monday, 2 columns)
'PB V0.90
Dim dt3 As Date, dt4 As Date, dt5 As Date
Dim i As Long

If dt2 <= dt1 Then
count_hours = 0#
Exit Function
End If

If (Int(dt1) = Int(dt2)) Then
dt3 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt3 dt2 Then dt3 = dt2
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt4 = Int(dt1)
Else
dt4 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt4 < dt1 Then dt4 = dt1
End If
count_hours = dt3 - dt4
Exit Function
End If

If CDbl(dt1) - Int(CDbl(dt1)) = vwh(Weekday(dt1, 2), 2) Then
dt3 = 0#
Else
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt3 = Int(dt1)
Else
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt3 < dt1 Then dt3 = dt1
End If
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 2) - dt3
End If

If CDbl(dt2) - Int(CDbl(dt2)) <= vwh(Weekday(dt2, 2), 1) Then
dt5 = 0#
Else
dt5 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt5 dt2 Then dt5 = dt2
If vwh(Weekday(dt2, 2), 1) = 0 Then
dt5 = dt5 - Int(dt2)
Else
dt5 = dt5 - Int(dt2) - vwh(Weekday(dt2, 2), 1)
End If
End If

If Int(dt2) - Int(dt1) 1 Then
dt4 = 0#
For i = Int(dt1) + 1 To Int(dt2) - 1
dt4 = dt4 + vwh(Weekday(i, 2), 2) - vwh(Weekday(i, 2), 1)
Next i
End If

count_hours = dt3 + dt4 + dt5

End Function


Insert a macro module: Press ALT + F11, insert module, copy text above
into the module, go back to worksheet.

Then enter into A1:B7
06:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 00:00

and into E1
10/07/2007
into F1
17/07/2007
then
=count_hours(E1,F1,A1:B7)
will result in 5.75 or 5 18:00, depending on how you format this
result cell.

Regards,
Bernd

PS: This has originally been sent:
http://groups.google.de/group/micros...5d35c685098e59

workday include saturday
 

Bernd je napisal:
Hello,

I suggest to use my UDF:

Function count_hours(dt1 As Date, dt2 As Date, _
vwh As Variant) As Date
'Returns time between dt1 and dt2 but counts only
'hours given in table vwh: for example
'04:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'01:00 23:30
'09:00 23:30
'00:00 00:00
'This table defines hours to count for each day
'of the week (starting with Monday, 2 columns)
'PB V0.90
Dim dt3 As Date, dt4 As Date, dt5 As Date
Dim i As Long

If dt2 <= dt1 Then
count_hours = 0#
Exit Function
End If

If (Int(dt1) = Int(dt2)) Then
dt3 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt3 dt2 Then dt3 = dt2
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt4 = Int(dt1)
Else
dt4 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt4 < dt1 Then dt4 = dt1
End If
count_hours = dt3 - dt4
Exit Function
End If

If CDbl(dt1) - Int(CDbl(dt1)) = vwh(Weekday(dt1, 2), 2) Then
dt3 = 0#
Else
If vwh(Weekday(dt1, 2), 1) = 0 Then
dt3 = Int(dt1)
Else
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 1)
If dt3 < dt1 Then dt3 = dt1
End If
dt3 = Int(dt1) + vwh(Weekday(dt1, 2), 2) - dt3
End If

If CDbl(dt2) - Int(CDbl(dt2)) <= vwh(Weekday(dt2, 2), 1) Then
dt5 = 0#
Else
dt5 = Int(dt2) + vwh(Weekday(dt2, 2), 2)
If dt5 dt2 Then dt5 = dt2
If vwh(Weekday(dt2, 2), 1) = 0 Then
dt5 = dt5 - Int(dt2)
Else
dt5 = dt5 - Int(dt2) - vwh(Weekday(dt2, 2), 1)
End If
End If

If Int(dt2) - Int(dt1) 1 Then
dt4 = 0#
For i = Int(dt1) + 1 To Int(dt2) - 1
dt4 = dt4 + vwh(Weekday(i, 2), 2) - vwh(Weekday(i, 2), 1)
Next i
End If

count_hours = dt3 + dt4 + dt5

End Function


Insert a macro module: Press ALT + F11, insert module, copy text above
into the module, go back to worksheet.

Then enter into A1:B7
06:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 24:00
00:00 00:00

and into E1
10/07/2007
into F1
17/07/2007
then
=count_hours(E1,F1,A1:B7)
will result in 5.75 or 5 18:00, depending on how you format this
result cell.

Regards,
Bernd

PS: This has originally been sent:
http://groups.google.de/group/micros...5d35c685098e59



Hi,
I guess I didn't explain well enough the calculation I want:

I want to calculate the end date&time of a project

The data I can use: the duration of a project (hours)
the start date&time


Working days are from Monday 6:00 to Saturday 14:00.


Monday: from 6:00 to 24:00
Tuesday: from 0:00 to 24:00
Wednesday: from 0:00 to 24:00
Thursday: from 0:00 to 24:00
Friday: from 0:00 to 24:00
Saturday: from 0:00 to 14:00


I want to calculate the end date&time starting with a project anytime
between Mon and Sat.

Thanx for help!

workday include saturday
 

Hello again,

You explained it well. I had to read it thoroughly...

I suggest to use my UDF add_hours:
http://www.sulprobil.com/html/count_hours.html

Please test it and use it at your own risk.

Regards,
Bernd