Date Calculation

fulanito_uk

New Member
Joined
Nov 30, 2015
Messages
31
Good morning,
I have a question about a sheet I am doing I will give some context.

I have 3 possible status options OPEN, MAYBE, CLOSED,
If the status is OPEN or MAYBE i need the NETWORKDAYS to calculate from DATE 1 (A) and DATE 3 (D)
If the status is CLOSED I need the NETWORKDAYS to calculate from DATE 2 (B) and DATE 3 (D).

1597585995290.png


If you need any more information for my side please let me know and Thank you very much for your help.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,084
Office Version
  1. 365
Platform
  1. Windows
How about
=NETWORKDAYS(IF(C2="closed",B2,A2),D2)
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
Book1
ABCDE
1DATE1DATE 2STATUSDATE 3NETWORK DAYS
201/01/2020OPEN02/02/202023
302/01/2020MAYBE02/02/202022
403/01/202001/01/2020CLOSED02/02/202023
Sheet3
Cell Formulas
RangeFormula
E2:E4E2=IF(OR(C2={"Open","Maybe"}),NETWORKDAYS.INTL(A2,D2,1),NETWORKDAYS.INTL(B2,D2,1))
 

fulanito_uk

New Member
Joined
Nov 30, 2015
Messages
31
Thanks so much for getting back to me, your formula worked fine on my test table but when I try to move it to my real one the formula doesn't seem to work..
if G5 says CERRADO (closed in spanish) it should take the dates from H5 and I5 if any other text is in G5 should take the dates from H5 and B1.

Thx! Any ideas what i'm doing wrong?


1597589976535.png
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I suppose it is returning the correct solution.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,084
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You need to swap the H5 & B5 in the formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,084
Office Version
  1. 365
Platform
  1. Windows
Oops misread & was looking at col C
=NETWORKDAYS(H5,IF(G5="cerrado",I5,B$1))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,539
Members
414,246
Latest member
allyciv

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
Top