Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > workday include saturday
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default 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"...
??



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 92
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 21
Default 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!

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 92
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you calculate workdays if Saturday is a workday? Tracy Parish Excel Worksheet Functions 3 April 18th 08 12:18 PM
Workday return a Saturday date Jessie Setting up and Configuration of Excel 2 March 23rd 07 07:47 PM
first saturday in a month Barry Excel Worksheet Functions 8 February 7th 06 03:05 PM
How to set Saturday as a working day Chatter_tk Setting up and Configuration of Excel 3 August 16th 05 02:38 PM
Include Saturday in the WORKDAY function kippi3000 Excel Worksheet Functions 9 December 31st 04 08:21 AM


All times are GMT +1. The time now is 03:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement