Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-909075%22%20slang%3D%22en-US%22%3ECalculating%20job%20estimates%20within%20working%20days%20and%20hours%20(in%20Excel).%20Help%20Please!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-909075%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20having%20a%20lot%20of%20trouble%20calculating%20estimates%20within%20working%20hours%20in%20excel.%26nbsp%3B%3C%2FP%3E%3CP%3EIll%20try%20to%20explain%20to%20the%20best%20of%20my%20ability%20what%20i%20am%20trying%20to%20accomplish.%3C%2FP%3E%3CP%3EAttached%20is%20my%20current%20documents%20for%20a%20reference%20(with%20added%20notes%20for%20clarity).%3C%2FP%3E%3CP%3EThe%20%3CU%3E%3CEM%3EScheduling%20Test%201%3C%2FEM%3E%3C%2FU%3E%26nbsp%3Bdocument%20is%20just%20connected%20for%20an%20easy%20way%20for%20me%20to%20sort%20priorities%20which%20doesn't%20apply%20here.%20The%20main%20document%20is%20%3CEM%3E%3CU%3EEstimate%20Test%201%3C%2FU%3E%3C%2FEM%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20formula%20that%20will%20add%20a%20specific%20amount%20of%20hours%20to%20a%20specific%20date%20and%20time%26nbsp%3B%3CSTRONG%3EONLY%3C%2FSTRONG%3E%20during%20%3CSTRONG%3EWORK%20HOURS%3C%2FSTRONG%3E%20(7%3A00%20am%20-%203%3A30%20pm%20with%20a%201%20hour%20lunch%20break%20at%2012%20PM%20-%201%20PM%26nbsp%3B%3CSTRONG%3E7.5%20hours%20total%3C%2FSTRONG%3E)%20and%20%3CSTRONG%3EONLY%3C%2FSTRONG%3E%20on%20%3CSTRONG%3EWEEKDAYS%3C%2FSTRONG%3E%20(Monday%20-%20Friday).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%20Start%20Date%2FTime%20%2B%2013%20hours%20%3D%20Completion%20time.%3C%2FP%3E%3CP%3EExample%3A%20Monday%2C%20October%2014%2C%207%3A00%20am%20%2B%2013%20hours%20%3D%20%3CEM%3E%3CU%3ETuesday%20October%2015%2C%201%3A30%20pm%3C%2FU%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EObtaining%20this%20formula%20will%20allow%20me%20to%20calculate%20the%20estimated%20completion%20time%20of%20the%20following%20processes%3A%20Prep%20(1%20worker)%2C%20Weld%20(1%20worker)%2C%20Clean%20(1%20worker)%2C%20and%20a%20final%20Completion%20Date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20step%20is%20determining%20the%20%3CEM%3E%3CU%3Ecompletion%20date%3C%2FU%3E%3C%2FEM%3E%20of%20the%20job%20entirely%2C%20which%20its%20value%20is%3A%3C%2FP%3E%3CP%3E%3CEM%3ECLEAN(1%20Worker)%3D((time%20and%20date%20value))%2B%26nbsp%3B%3C%2FEM%3E1%20day%20(or%20the%20remainder%20of%20the%20shift.).%20The%20solution%20being%20the%20following%20day%20at%20the%20start%20of%20the%20shift%20(7%3A00%20AM).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3BMonday%2C%20October%2014%2C%202%3A00%20pm%20%2B%20Remainder%20of%20the%20shift%20(OR%20start%20of%20the%20next%20shift)%20%3D%3CBR%20%2F%3ETuesday%20October%2015%207%3A00%20AM.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20Help!%20These%20formulas%20are%20beyond%20my%20skill%20level%20with%20excel%20and%20i%20would%20greatly%20appreciate%20anyone%20being%20able%20to%20point%20me%20in%20the%20right%20direction!%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-909075%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-910417%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20job%20estimates%20within%20working%20days%20and%20hours%20(in%20Excel).%20Help%20Please!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-910417%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F374276%22%20target%3D%22_blank%22%3E%40Shawn_Michaud%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20G2%2C%20copied%20across%20to%20I2%2C%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DF2%2BC2%2F24%2B%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E1%2F24*((MOD(F2%2BC2%2F24%2C1)%26gt%3B12%2F24)*(MOD(F2%2BC2%2F24%2C1)%26lt%3B13%2F24))%2B%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E15.5%2F24*(MOD(F2%2BC2%2F24%2C1)%26gt%3B15.5%2F24)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20apply%20the%20idea%20of%20the%20foregoing%20formula%20to%20suit%20your%20needs.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-915640%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20job%20estimates%20within%20working%20days%20and%20hours%20(in%20Excel).%20Help%20Please!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-915640%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20effort%20but...%3C%2FP%3E%3CP%3EThis%20formula%20was%20%3CSTRONG%3E%3CU%3Enot%3C%2FU%3E%3C%2FSTRONG%3E%20successful%20in%20making%20the%20calculations%20for%20my%20document.%3C%2FP%3E%3CP%3EI%20substituted%20it%20in%20and%20changed%20the%20values%20to%20the%20proper%20cells%2C%20and%20yet%20it%20seems%20to%20still%20be%20calculating%20Saturdays%20and%20well%20into%20the%20late%20hours%20of%20the%20night%20(3%20AM)%20.%20As%20you%20can%20see%20in%20the%20linked%20document.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20the%20test%20document%20provided%20for%20your%20solutions%20rather%20than%20creating%20a%20new%20document.%3C%2FP%3E%3CP%3EThe%20formula%20is%20required%20to%20work%20in%20the%20documents%20current%20format.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Shawn_Michaud
Occasional Contributor

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 Estimate Test 1.

 

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. 

 

1 Reply

@Shawn_Michaud 

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. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies