How to calculate completion date/time ignoring nonworking hours

[sosession]

Known Variables

• A1 = start date and time (6/16/15 6:00AM)
• B1 = estimated hours to complete work order (15)
• Work schedule is 6AM to 6PM, weekdays only

What formula can I use to calculate the end date/time, ignoring nonworking hours? It currently says that the estimated completion will be 6/16/15 9:00PM. However, that is outside of working hours. The correct estimated completion date/time should be 6/17/15 9:00AM. How do I ignore nonworking hours and weekends? Please help!

 

[James006]

Hi,

If B1 holds 6:00 AM and B2 holds 6:00 PM ... you could test :

=WORKDAY(A1,CEILING((A2+MOD(A1,1)-B1)/(B2-B1),1)-1)+MOD(A1,1)+A2-CEILING(MOD(A1,1)+A2-B1,B2-B1)+B2-B1

 

[sosession]

Hi,

If B1 holds 6:00 AM and B2 holds 6:00 PM ... you could test :

=WORKDAY(A1,CEILING((A2+MOD(A1,1)-B1)/(B2-B1),1)-1)+MOD(A1,1)+A2-CEILING(MOD(A1,1)+A2-B1,B2-B1)+B2-B1

I'm not following. Please elaborate on how this helps. Thanks!

 

[James006]

Hi,

Before elaborating ... have you tried to apply the formula ...???

 

[sosession]

Yes, the cell reads 6/16/15 12:00pm

 

[James006]

Fair enough ... But is your result right or wrong ...???

 

[sosession]

Well I'm lost because I had the start day and time in a1, wo hours in b1. You changed b1 and b2 so I don't even have a number of work order hours I'm trying to compute now.

 

[James006]

Sorry for the confusion ... when building the formula for you ... I used :

• A1 = start date and time
• A2 = estimated hours to complete work order
• B1 = 6AM
• B2 = 6PM

 

[sosession]

Thank you for clarifying. Unfortunately, the cell comes back with an error (I used 14 for my work order hours).

 

[James006]

Better than long explanations ... a working spreadsheet ...

Test Sosession.xlsx

All credit goes to Barry ...