Calculate future dates

J

jgalas

Board Regular
Joined
Jul 4, 2011
Messages
99
Office Version
  1. 2013
Platform
  1. Windows
Hi! I have a task that takes 50 hours. it can start on any business day and runs 2 hours on Tuesdays and 2 hours on Wednesdays. I want to know the end date of the task.
Ex: if the start date is 03/22/2023, the end date will be 06/20/2023 excluding a holiday on Tuesday (holidays and vacation days can be in a column)
can someone give me a tip please
 
Sort by date Sort by votes
jgalas said:
How to automatically change expression "1100111"
Click to expand...
You cannot automatically change it. Also you would need to change the 25 in the function to account for the other days.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
jgalas said:
It may happen that the task is distributed over 3 days for example one day - 2 hours and 1 hour over two different days.
How to automatically change expression "1100111"
Click to expand...
the 1100111 is the weekly on/off schedule. You can't really change it throughout your columns. And assuming that the additional work days are only Thursdays
.... you can change it to 1000111 and then put the Thursdays notworked s in the holiday schedule.
 
Last edited:
Upvote 0
Fluff said:
You cannot automatically change it. Also you would need to change the 25 in the function to account for the other days.
Click to expand...
I can have 2 hours in a Tuesday, 1 hour in a Wednesday and 1 hour in a Friday. How i can resolve if it wil happen?
 
Upvote 0
That depends on what the start date is.
 
Upvote 0
if you want a 3rd day of hte week, say Thursday, every other week then put the off thursdays in the Holiday schedule.
But, I have no idea how to handle different hours per day. At least with the WORKDAY.INTL function.

Here is some thursdays, and all tuesday and wednesdays worked, and one holiday:
Book1
ABCDEFGH
1StartHoursMax Hrs Per Day
2Monday, March 20, 2023502Tuesday, May 30, 2023Holidays
3Tuesday, March 21, 2023502Tuesday, May 30, 2023Thursday, March 23, 2023
4Wednesday, March 22, 2023502Wednesday, May 31, 2023Thursday, March 30, 2023
5Thursday, March 23, 2023502Wednesday, June 7, 2023Thursday, April 13, 2023
6Friday, March 24, 2023502Wednesday, June 7, 2023Thursday, April 27, 2023
7Saturday, March 25, 2023502Wednesday, June 7, 2023Thursday, May 11, 2023
8Sunday, March 26, 2023502Wednesday, June 7, 2023Thursday, May 18, 2023
9Monday, March 27, 2023502Wednesday, June 7, 2023Thursday, June 1, 2023
10Tuesday, March 28, 2023502Wednesday, June 7, 2023Tuesday, June 6, 2023
11Wednesday, March 29, 2023502Thursday, June 8, 2023Thursday, June 15, 2023
12Thursday, June 22, 2023
13
Sheet1
Cell Formulas
RangeFormula
B7:B11B7=B6+1
E2:E11E2=WORKDAY.INTL(B2-1,C2/2,"1000111",$H$3:$H$12)
 
Upvote 0
Fluff said:
That depends on what the start date is.
Click to expand...
Same example 50 hours but 3 working days, 2 hours in a Tuesday, 1 hour in a Wednesday and 1 hour in a Friday, starting day 22/03/2023 Holidays in D2:D7
 
Upvote 0
awoohaw said:
if you want a 3rd day of hte week, say Thursday, every other week then put the off thursdays in the Holiday schedule.
But, I have no idea how to handle different hours per day. At least with the WORKDAY.INTL function.

Here is some thursdays, and all tuesday and wednesdays worked, and one holiday:
Book1
ABCDEFGH
1StartHoursMax Hrs Per Day
2Monday, March 20, 2023502Tuesday, May 30, 2023Holidays
3Tuesday, March 21, 2023502Tuesday, May 30, 2023Thursday, March 23, 2023
4Wednesday, March 22, 2023502Wednesday, May 31, 2023Thursday, March 30, 2023
5Thursday, March 23, 2023502Wednesday, June 7, 2023Thursday, April 13, 2023
6Friday, March 24, 2023502Wednesday, June 7, 2023Thursday, April 27, 2023
7Saturday, March 25, 2023502Wednesday, June 7, 2023Thursday, May 11, 2023
8Sunday, March 26, 2023502Wednesday, June 7, 2023Thursday, May 18, 2023
9Monday, March 27, 2023502Wednesday, June 7, 2023Thursday, June 1, 2023
10Tuesday, March 28, 2023502Wednesday, June 7, 2023Tuesday, June 6, 2023
11Wednesday, March 29, 2023502Thursday, June 8, 2023Thursday, June 15, 2023
12Thursday, June 22, 2023
13
Sheet1
Cell Formulas
RangeFormula
B7:B11B7=B6+1
E2:E11E2=WORKDAY.INTL(B2-1,C2/2,"1000111",$H$3:$H$12)
Click to expand...
Thanks for the support. the function WORKDAY.INTL works almost everytime
 
Upvote 0
You will struggle to get it accurate as it will depend on whether the start is a Tue, Wed or Fri & what holidays you have.
 
Upvote 0
Fluff said:
You will struggle to get it accurate as it will depend on whether the start is a Tue, Wed or Fri & what holidays you have.
Click to expand...
Ok.
If have a column with dates for Mondays, another for Tuesdays, another for Wednesdays, others for Thursdays,... starting at 22/03/2023 and count Tuesday 2 hours, Thursday 1 hour and Friday 1 hour until make 50 hours and return the last date (holiday - 25/4/2023 in D2:D7) - end date 20/6/2023
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Calculating future date excluding Public Holiday, Weekends and Backdate specific date
Replies
1
Views
64
etaf
etaf
S
  • Solved
Calculate Revert Time
Replies
6
Views
405
felixstraube
F
E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
331
KRice
K
W
  • Question
Holiday hours Calculation
Replies
2
Views
396
Wrenn_6869
W
E
  • Solved
How to use WORKDAY to calculate the Final Training Day and exclude Weekends and Public Holidays
Replies
5
Views
236
Fluff
Fluff

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

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