workday include saturday

S

stremetzky

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
 
B

Bob Phillips

=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)
 
S

stremetzky

=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)











- Show quoted text -
Click to expand...

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"...
??
 
B

Bernd

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/micro...t&q=count_hours&rnum=1&hl=de#b25d35c685098e59
 
S

stremetzky

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/micro...t&q=count_hours&rnum=1&hl=de#b25d35c685098e59
Click to expand...



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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Workday Formula to include date 0
how do I calculate a week days hours with the 24hour clock 5
Calculate elapsed time excluding specific timeframe 10
Excel Facetime Measurement 1
Date Time increment with additional condition of skipping weekends 13
Break Calculation 1
IF(M2="","",(NETWORKDAYS(F2,M2))+TIME(17,0,0)-F2+M2-TIME(8,0,0)) 7
Time query 9

Top