workdays again

[brettsouth]

Hi to all,
Can anyone please help with this problem,

I would like to calculate the date and time a job on a Machin
finishes, given a known start date.

The problem being I need 5 days a week only with a holiday list plu
the work day starts at 07:00:00 am and ends at 04:00:00 pm

for example cell A1 start date of 09/08/2004 07:00:00am,
Cell B1 has 3.5 representing 3.5 work days

The current formula in cell C1 works out well for full machine days bu
when the machine hours are 3.5 the time adjustment moves very littl
from that of when the Machine hours were 3 full days ie it does no
move by a half day

The formula supplied is

WORKDAY(INT(A1),INT(B1),Holiday_List)+MOD(A1,1)+(MOD(B1,1)/24)*(7/24)

Any ideas File attache

Attachment filename: workdays.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=64617

 

[Frank Kabel]

Hi
you may post a follow-up in your original thread

I think I made a mistake in providing you with this
formula (divided too much..). Try:

WORKDAY(INT(A1),INT(B1),Holiday_List)+MOD(A1,1)+MOD(B1,1)*
(7/24)

 

[Ron Rosenfeld]

Hi to all,
Can anyone please help with this problem,

I would like to calculate the date and time a job on a Machine
finishes, given a known start date.

The problem being I need 5 days a week only with a holiday list plus
the work day starts at 07:00:00 am and ends at 04:00:00 pm

for example cell A1 start date of 09/08/2004 07:00:00am,
Cell B1 has 3.5 representing 3.5 work days

The current formula in cell C1 works out well for full machine days but
when the machine hours are 3.5 the time adjustment moves very little
from that of when the Machine hours were 3 full days ie it does not
move by a half day

The formula supplied is

WORKDAY(INT(A1),INT(B1),Holiday_List)+MOD(A1,1)+(MOD(B1,1)/24)*(7/24)

Not a simple formula for me. Probably easier in VBA but I think this formula
should work. Perhaps someone can simplify this.

The named ranges should be obvious. After you paste the formula into the
formula bar, name your ranges appropriately and try it out.

A fractional workday is figured based on the number of hours in the workday.
So if the workday starts at 7AM and ends at 4PM (9 hrs), 3.5 workdays would
compute to 3 days 4 hrs 30 minutes.

So in your example, the finish would compute to 9/13/2004 11:30 AM.

I have assumed that you will not enter a JobStartDateTime earlier than the
WorkDayStartTime.

Try this formula:

=WORKDAY(JobStartDateTime,JobDurationDays+
+(MOD(JobStartDateTime,1)+MOD(JobDurationDays,1)*
(WorkDayEndTime-WorkDayStartTime)>WorkDayEndTime),Holidays)+
IF(MOD(JobDurationDays,1)*(WorkDayEndTime-WorkDayStartTime)+
MOD(JobStartDateTime,1)>WorkDayEndTime,MOD(JobDurationDays,1)*
(WorkDayEndTime-WorkDayStartTime)+MOD(JobStartDateTime,1)-
WorkDayEndTime+WorkDayStartTime,MOD(JobDurationDays,1)*
(WorkDayEndTime-WorkDayStartTime)+MOD(JobStartDateTime,1))


--ron

 

[Ron Rosenfeld]

Hi
you may post a follow-up in your original thread

I think I made a mistake in providing you with this
formula (divided too much..). Try:

WORKDAY(INT(A1),INT(B1),Holiday_List)+MOD(A1,1)+MOD(B1,1)*
(7/24)

Frank,

I don't think your formula accounts for the fact that, in my interpretation,
the day should "wrap" to the next day if the finish time takes the ending time
past the end of the workday. Also, I think you assume there are 7 hours in the
workday and, at least in one of his posts, the OP had the day going from 7 AM
to 4 PM -- 9 hrs

For example, if the start time is Thursday 01-Jul-2004 1:00 PM
and the job duration is 3.5 days, your formula gives Tuesday 06-Jul-2004 4:30
PM instead of 8:30 the next morning. (With the Workday starting at 7AM and
ending at 4PM)

Of course, some of my interpretations could be incorrect, too


--ron

 

[Frank Kabel]

Hi Ron
no you're correct in your analysis. I just adapted my formula from a
previous post (in which the OP stated the day ends at 14:00). Also the
restriction is that I assumed the working time ALWAYS starts at 07:00
or at least that there's no roll-over

 

[Myrna Larson]

Here's a VBA solution that seems to work for me. To use it, you need to go to
the VB Editor (ALT+F11), select your workbook over in the Project pane on the
upper left, then Insert/Module, and paste the code below (what's between the
lines of tildes) into the code window that will appear on the right.

Once you've done that, assuming the starting date in A1, the number of days in
B1, and your holiday list in a range named Holidays, the worksheet formula is

=WorkdayX(A1,B1,Holidays)

BTW, the code assumes that the StartDate that you specify is valid, i.e. it IS
a work day and the time is between 7:00 and 16:00. It doesn't check for
invalid start dates and/or times.

'~~~~~
Function WorkdayX(StartDate As Date, NumDays As Double, _
Optional Holidays As Range = Nothing)
Dim EndDate As Double
Dim EndTime As Double

Const DayStart As Double = 7 / 24
Const DayEnd As Double = 16 / 24

If Holidays Is Nothing Then
EndDate = WORKDAY(StartDate, NumDays)
Else
EndDate = WORKDAY(StartDate, Fix(NumDays), Holidays)
End If

'WORKDAY returns time component of 0, so move
'this forward to same time as starting time
EndDate = EndDate + (StartDate - Fix(StartDate))

'add hours for partial day: 9 hours per workday
If NumDays <> Fix(NumDays) Then
EndDate = EndDate + (NumDays - Fix(NumDays)) * 9 / 24
'check whether time is past end of workday
EndTime = EndDate - Fix(EndDate)
If EndTime > DayEnd Then
EndTime = (EndTime - DayEnd) + DayStart 'excess hours to next day
EndDate = Fix(EndDate) + 1 + EndTime
End If
End If
WorkdayX = EndDate
End Function
'~~~~~

 

[unlikeKansas]

Hi brettsouth,

I played around with the formula that Frank posted the other day and the
following seems to fit your requirements.

=IF((MOD(A1,1)+(MOD(B1,1))*9/24)>0.66667,WORKDAY(INT(A1),INT(B1),list_of_holidays)+(MOD(A1,1)+(MOD(B1,1))*9/24+0.625),WORKDAY(INT(A1),INT(B1),list_of_holidays)+(MOD(A1,1)+(MOD(B1,1))*9/24)).

If you set validation in cell A1 as custom, with the following formula it
will stop the operator entering an invalid star time (i.e. before 07:00 or
after 16:00):

=IF(MOD(A1,1)<0.29166,0,IF(MOD(A1,1)>0.58334,0,1)).

However nothing yet can stop them entering an invalid date (e.g. Saturday,
Holiday, etc.)

unlikeKansas