- 540K Members
- 2,448 Online
- 644K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Calculating job estimates within working days and hours (in Excel). Help Please!

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
Home
## Calculating job estimates within working days and hours (in Excel). Help Please!

- Home
- :
- Excel
- :
- General Discussion
- :
- Calculating job estimates within working days and hours (in Excel). Help Please!

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-11-2019 08:34 AM

Hello,

I'm currently having a lot of trouble calculating estimates within working hours in excel.

Ill try to explain to the best of my ability what i am trying to accomplish.

Attached is my current documents for a reference (with added notes for clarity).

The __ Scheduling Test 1__ document is just connected for an easy way for me to sort priorities which doesn't apply here. The main document is

I'm looking for a formula that will add a specific amount of hours to a specific date and time **ONLY** during **WORK HOURS** (7:00 am - 3:30 pm with a 1 hour lunch break at 12 PM - 1 PM **7.5 hours total**) and **ONLY** on **WEEKDAYS** (Monday - Friday).

Example: Start Date/Time + 13 hours = Completion time.

Example: Monday, October 14, 7:00 am + 13 hours = __Tuesday October 15, 1:30 pm__

Obtaining this formula will allow me to calculate the estimated completion time of the following processes: Prep (1 worker), Weld (1 worker), Clean (1 worker), and a final Completion Date.

The next step is determining the * completion date* of the job entirely, which its value is:

*CLEAN(1 Worker)=((time and date value))+ *1 day (or the remainder of the shift.). The solution being the following day at the start of the shift (7:00 AM).

Example: Monday, October 14, 2:00 pm + Remainder of the shift (OR start of the next shift) =

Tuesday October 15 7:00 AM.

Please Help! These formulas are beyond my skill level with excel and i would greatly appreciate anyone being able to point me in the right direction!

Thanks.

Labels:

5 Replies

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-11-2019 11:53 PM

In the attached file, the formula in G2, copied across to I2, is:

**=F2+C2/24+****1/24*((MOD(F2,1)<12/24)*(MOD(F2+C2/24,1)>12/24)*(MOD(F2+C2/24,1)<15.5/24))+****16.5/24*((MOD(F2,1)<12/24)*(MOD(F2+C2/24,1)>15.5/24))+****15.5/24*((MOD(F2,1)>12/24)*(MOD(F2+C2/24,1)>15.5/24))+****2*((MOD(F2+C2/24,1)>15.5/24)*(WEEKDAY(F2+C2/24)>5))**

I hope that the idea of the foregoing formula will suit your needs.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2019 02:17 PM

The formula could work great, but has complications when using it in the document i provided.

I assume that is why you decided to create a second document and use percentages instead of formulas to obtain number values. (Which defeats the purpose of this formula if its not usable in the provided documents format).

Its works fine if the PREP(T), WELD (T), or CLEAN(T) value is provided as a single number.

But DOES NOT work if a formula is needed to obtain this number (which it is in every case).

It will continue to calculate **outside **working ours and into **weekends** which defeats the purpose entirely.

Also for the COMPLETED date portion I was requesting a formula to add +1 day to the CLEAN(T) value (with the time value always landing at 7:00 am) if possible.

Is anyone able to help me make this work?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-21-2019 02:23 PM

You can see how this solution was unsuccessful in the attached document.

(Its calculating out of working hours and on weekends).

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2019 03:04 AM

SolutionIn the attached version of your file, the formula in L2, copied across to N2, is:

**=K2+INT(D2/7.5)+MOD(D2,7.5)/24+****IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),****0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+****(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+****(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24+****2*(K2+INT(D2/7.5)+MOD(D2,7.5)/24+****IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),****0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+****(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+****(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24>****INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)**

In O2, the formula is:

**=WORKDAY(N2,1)+****7/24**

I hope that the foregoing formulas will now return your expected results, as shown here:

Best Response confirmed by
Shawn_Michaud (Occasional Contributor)

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-22-2019 08:12 AM - edited 10-22-2019 08:41 AM

This formula works great thank you very much!

If I wanted to apply this to say two or three workers which values should i be switching out in the provided formula if possible? I've tried swapping out all of the 7.5 values in the formula for 15 but it doesn't seem to be working. I assume I may be approaching this the wrong way?

Example:

1 worker = 7.5 working hours/day

2 workers = 15 working hours/day

3 workers = 22.5 working hours/day

Attempted conversion to 2 workers example:

=K2+INT(D2/7.5)+MOD(D2,7.5)/24+

IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),

0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+

(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+

(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24+

2*(K2+INT(D2/7.5)+MOD(D2,7.5)/24+

IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),

0,15.5),CHOOSE((MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>7/24)+

(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>12/24)+

(MOD(K2+INT(D2/7.5)+MOD(D2,7.5)/24,1)>15.5/24),0,1,16.5))/24>

INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

converted to:

=K2+INT(D2/15)+MOD(D2,15)/24+

IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),

0,15.5),CHOOSE((MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>7/24)+

(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>12/24)+

(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),0,1,16.5))/24+

2*(K2+INT(D2/15)+MOD(D2,15)/24+

IF(MOD(K2,1)>=13/24,CHOOSE(1+(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),

0,15.5),CHOOSE((MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>7/24)+

(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>12/24)+

(MOD(K2+INT(D2/15)+MOD(D2,15)/24,1)>15.5/24),0,1,16.5))/24>

INT(K2)+CHOOSE(WEEKDAY(K2),5,4,3,2,1,0,6)+15.5/24)

I assume I'm doing this the wrong way.

Are there specific areas within this formula that i may be able to substitute values to calculate these days with 2 or even 3 workers instead of one?

Related Conversations

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft