Weekends in date based formulas

Sweedler

Board Regular
Joined
Nov 13, 2020
Messages
124
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
Hello

I have a large document for delivery of goods. In one column I have the agreed upon date for delivery of the finished product. In the columns next to I have calculated dates for several steps that precede the product being ready to deliver. These formulas first calculate the number of days from the end date that a step needs to be completed by and then that answer is removed from the end date to get the date for that step's completion. But the formula only calculates the number of days, and I need it to only count that answer as weekdays. Does anyone know a formula that would take that into consideration.

Thank you
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sounds like you need either the workday or networkdays function.
 
Upvote 0
Sounds like you need either the workday or networkdays function.
Hi

So how would that work. If I have the date 2024-03-26 (a Tuesday) as my end date and packaging needs to take place 5 days in advance of that. So the date it returns should be the 19th, but the formula "=DATECELL-5" would return the 21:st. How do I adjust and adapt to get the correct date back?
 
Upvote 0
Something like this maybe:
Book1
AB
1
2DATECELL
32024-03-26
4
5
62024-03-19
Sheet4
Cell Formulas
RangeFormula
B3B3=DATE(2024,3,26)
B6B6=WORKDAY.INTL(DATECELL,-5,1)
Named Ranges
NameRefers ToCells
DATECELL=Sheet4!$B$3B6
 
Upvote 1
You would simply use
Excel Formula:
=workday(datecell,-5)
 
Upvote 1
Solution
Work Days.xlsm
ABC
1
2Tue 26-Mar-2024
35
4Tue 19-Mar-2024
5
1b
Cell Formulas
RangeFormula
C4C4=WORKDAY(C2,-C3)


The formula could also consider holidays.
 
Upvote 1
Glad we could help & thanks for the feedback.
 
Upvote 1

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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