Adding time to a date excluding weekends and holidays

T

ToddG

Board Regular
Joined
Sep 28, 2004
Messages
82
I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. Any input would be appreciated.

I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.

Has anyone done anything similar to this or could point me to some good info on it?

Thanks very much in advance
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
It's possible but you need some fairly complex formulas, especially if you have multiple breaks. Will the start time always be within the working hours. How many breaks are there?
 
Upvote 0
Well the "working time" I was thinking could just come from a cell value, either manually entered or calculated which I already have. So basically the only issue would be the weekends and holidays.

The start time should always be within the working hours.
 
Upvote 0
but if you want an accurate finish time then you'll have to know the actual working times. For example if I say the start time is 16:00 today and I want to add 12 working hours at 10 hours per day then I'm adding a day and 2 hours, whether that will take me to a time late on Monday or early on Tuesday depends on the daily finish time, e.g. daily hours 09:00 to 19:00 will take me to 18:00 on Monday but daily hours 07:00 to 17:00 will take me to 08:00 on Tuesday.
 
Upvote 0
Here's an example of what you can do if you just define the MF working times....

If you have your start date/time in A1 then hours to add in B1 (in time format e.g. 36:00 for 36 hours) then this formula in C1 will give end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

where D2 and E2 shows the start and end times of the working day, e.g. 08:00 in D2 and 17:00 in E2

doesn't take account of breaks in the day

Note: WORKDAY is an Analysis ToolPak add-in function (unles you're using Excel 2007)
 
Upvote 0
this point interests me too.
For the moment I absolutely ignore the time and only count days forward. I found a solution for working days only excluding weekends. But when an activity takes only 2 hours, the next one starts already the following day.

I have put time needed to complete the activity in one cell.
How to I now include the time rule /lets assume from 08 AM to 5 PM / ?
 
Upvote 0
barry houdini said:
Here's an example of what you can do if you just define the MF working times....

If you have your start date/time in A1 then hours to add in B1 (in time format e.g. 36:00 for 36 hours) then this formula in C1 will give end date/time

=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$2)/(E$2-D$2),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$2,E$2-D$2)+E$2-D$2

where D2 and E2 shows the start and end times of the working day, e.g. 08:00 in D2 and 17:00 in E2

doesn't take account of breaks in the day

Note: WORKDAY is an Analysis ToolPak add-in function (unles you're using Excel 2007)
Click to expand...


I have tried your formula. The result is not good. I set working hours per day from 08h00 to 05h00 and time needed to 12 hrs. still getting the same day as starting day.
 
Upvote 0
deckerp said:
I have tried your formula. The result is not good. I set working hours per day from 08h00 to 05h00 and time needed to 12 hrs. still getting the same day as starting day.
Click to expand...

Are the start and end times in valid time formats? should be like 08:00 and 17:00 or 08:00 AM and 05:00 PM (not 05:00 as that will be read as 5 AM)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
226
Fluff
Fluff
R
  • Question
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
7
Views
173
Alex Blakenburg
A
C
  • Question
Finding weekends and holidays within a date range
Replies
1
Views
293
ColinPearsonEIT
C
E
  • Question
Calculating No of Days between 2 dates INCLUDING Weekends but EXCLUDING Public Holidays
Replies
1
Views
357
Peter_SSs
Peter_SSs
F
  • Solved
Take date/time and calculate the time in between excluding weekends and including partial days (looking at full 24 hour days)
Replies
4
Views
217
freeb1893
F

Forum statistics

Threads
1,214,386
Messages
6,119,215
Members
448,874
Latest member
b1step2far

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