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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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

C
  • Question Question
Calculating consecutive days excluding weekends and holidays
Replies
4
Views
84
croud
C
P
  • Question Question
How to subtract 2 dates and times from each other excl weekends, holidays and only considering working hours during the day?
Replies
1
Views
124
Micron
M
R
  • Solved
Overtime based on weekends AND holidays
2
Replies
12
Views
818
rockson
R
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
937
Fluff
Fluff
B
  • Solved
Issue with finding time difference taking account of weekends and workdays.
2
Replies
15
Views
402
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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