Calculating next start Date and time

M

MazurK

New Member
Joined
Dec 16, 2021
Messages
6
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
My hours of operation are Weekdays 7:00 a.m. to 11:30 p.m. I am looking for a formula that will tell me when the next job will start, if the job previous ended at 10:30 p.m. Formula I am using for my start date/time takes the end date/time of the previous job and adds set up (i.e. 4 hours). My end date/time uses the start date/time and adds machine hours + change over.

Formula used:
=WORKDAY(A2,INT(B2/8)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,1,0),$G$2:$G$14)+IF(TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)>$E$2,$D$2+TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0)-$E$2,TIME(HOUR(A2),MINUTE(A2),SECOND(A2))+TIME(MOD(B2,8),MOD(MOD(B2,8),1)*60,0))
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
I'm not sure how you are really trying to implement this, but you can rescale how excel does time, so you can just add 4:00 hours or 6:30 hours or whatever, and it will just automatically skip over the non working hours. like this:
MrExcelPlayground5.xlsx
DEFG
217:00:00 AM
2211:30:00 PM0.68751.454545455
23
24
25
2612/16/2021 7:004:004454612/16/2021 11:00
2712/16/2021 7:304:0044546.030312/16/2021 11:30
2812/16/2021 8:004:0044546.0606112/16/2021 12:00
2912/16/2021 8:304:0044546.0909112/16/2021 12:30
3012/16/2021 9:004:0044546.1212112/16/2021 13:00
3112/16/2021 9:304:0044546.1515212/16/2021 13:30
3212/16/2021 10:004:0044546.1818212/16/2021 14:00
3312/16/2021 10:304:0044546.2121212/16/2021 14:30
3412/16/2021 11:004:0044546.2424212/16/2021 15:00
3512/16/2021 11:304:0044546.2727312/16/2021 15:30
3612/16/2021 12:004:0044546.3030312/16/2021 16:00
3712/16/2021 12:304:0044546.3333312/16/2021 16:30
3812/16/2021 13:004:0044546.3636412/16/2021 17:00
3912/16/2021 13:304:0044546.3939412/16/2021 17:30
4012/16/2021 14:004:0044546.4242412/16/2021 18:00
4112/16/2021 14:304:0044546.4545512/16/2021 18:30
4212/16/2021 15:004:0044546.4848512/16/2021 19:00
4312/16/2021 15:304:0044546.5151512/16/2021 19:30
4412/16/2021 16:004:0044546.5454512/16/2021 20:00
4512/16/2021 16:304:0044546.5757612/16/2021 20:30
4612/16/2021 17:004:0044546.6060612/16/2021 21:00
4712/16/2021 17:304:0044546.6363612/16/2021 21:30
4812/16/2021 18:004:0044546.6666712/16/2021 22:00
4912/16/2021 18:304:0044546.6969712/16/2021 22:30
5012/16/2021 19:004:0044546.7272712/16/2021 23:00
5112/16/2021 19:304:0044546.7575812/17/2021 7:00
5212/16/2021 20:004:0044546.7878812/17/2021 7:30
5312/16/2021 20:304:0044546.8181812/17/2021 8:00
5412/16/2021 21:004:0044546.8484812/17/2021 8:30
5512/16/2021 21:304:0044546.8787912/17/2021 9:00
5612/16/2021 22:004:0044546.9090912/17/2021 9:30
5712/16/2021 22:304:0044546.9393912/17/2021 10:00
5812/16/2021 23:004:0044546.969712/17/2021 10:30
5912/16/2021 23:304:004454712/17/2021 11:00
6012/17/2021 7:004:004454712/17/2021 11:00
6112/17/2021 7:304:0044547.030312/17/2021 11:30
6212/17/2021 8:004:0044547.0606112/17/2021 12:00
6312/17/2021 8:304:0044547.0909112/17/2021 12:30
6412/17/2021 9:004:0044547.1212112/17/2021 13:00
6512/17/2021 9:304:0044547.1515212/17/2021 13:30
6612/17/2021 10:004:0044547.1818212/17/2021 14:00
6712/17/2021 10:304:0044547.2121212/17/2021 14:30
6812/17/2021 11:004:0044547.2424212/17/2021 15:00
6912/17/2021 11:304:0044547.2727312/17/2021 15:30
7012/17/2021 12:004:0044547.3030312/17/2021 16:00
7112/17/2021 12:304:0044547.3333312/17/2021 16:30
7212/17/2021 13:004:0044547.3636412/17/2021 17:00
7312/17/2021 13:304:0044547.3939412/17/2021 17:30
7412/17/2021 14:004:0044547.4242412/17/2021 18:00
7512/17/2021 14:304:0044547.4545512/17/2021 18:30
7612/17/2021 15:004:0044547.4848512/17/2021 19:00
7712/17/2021 15:304:0044547.5151512/17/2021 19:30
7812/17/2021 16:004:0044547.5454512/17/2021 20:00
7912/17/2021 16:304:0044547.5757612/17/2021 20:30
8012/17/2021 17:004:0044547.6060612/17/2021 21:00
8112/17/2021 17:304:0044547.6363612/17/2021 21:30
8212/17/2021 18:004:0044547.6666712/17/2021 22:00
8312/17/2021 18:304:0044547.6969712/17/2021 22:30
8412/17/2021 19:004:0044547.7272712/17/2021 23:00
8512/17/2021 19:304:0044547.7575812/18/2021 7:00
8612/17/2021 20:004:0044547.7878812/18/2021 7:30
8712/17/2021 20:304:0044547.8181812/18/2021 8:00
8812/17/2021 21:004:0044547.8484812/18/2021 8:30
8912/17/2021 21:304:0044547.8787912/18/2021 9:00
9012/17/2021 22:004:0044547.9090912/18/2021 9:30
9112/17/2021 22:304:0044547.9393912/18/2021 10:00
9212/17/2021 23:004:0044547.969712/18/2021 10:30
9312/17/2021 23:304:004454812/18/2021 11:00
Sheet21
Cell Formulas
RangeFormula
E22E22=D22-D21
F22F22=1/E22
F26:F93F26=INT(D26)+(MOD(D26,1)-$D$21)*$F$22
G26:G93G26=INT(F26+E26*$F$22)+MOD((F26+E26*$F$22),1)/$F$22+$D$21
D27:D59,D61:D93D27=D26+1/48
D60D60=D59+7.5/24
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

C
  • Question
Elapsed day, hours, minutes minus weekends
Replies
6
Views
385
Scott Huish
Scott Huish
A
  • Solved
Production time calculation within the working day accounting for breaks
2
Replies
16
Views
642
felixstraube
F
K
  • Question
Production Time Calculator
Replies
4
Views
183
Drrellik
Drrellik
S
  • Solved
Calculate Revert Time
Replies
6
Views
403
felixstraube
F
C
  • Solved
Getting Minutes from time when rolling over to next day
Replies
5
Views
187
cknnugget
C

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top