Solved Business Days Between Dates in Excel

July 6, 2020 at 09:41:00
Specs: Windows 10
I'm trying to create a formula in E2, F2 and G2 to compute the number of days (M-F only) between each stage. A2 is the Start Date (SD), B2 is the Review Date (RD), C2 is the Approval Date (AD) and D2 is the Design Date (DD). If I put dates on columns A to D, E2 will show the number of workdays between SD and RD (workdays spent in writing stage), F2 will show the number of workdays between RD and AD (workdays in the Review queue) and G2 will show the number of workdays between AD and DD (workdays in the Approval queue). Note: I want to exclude the entered date in the first count (ex: If I entered 7/1 in A2, I want it to show only 1 day in E2 if I enter a date of 7/2 in B2). Lastly, if each stage is completed on the same day, I want the results in E2 to G2 to show "0".


    A    B    C    D    E    F     G
1  SD   RD   AD   DD   DWS   DRQ   DAQ
2							

message edited by CuriousChris


See More: Business Days Between Dates in Excel


#1
July 6, 2020 at 10:09:05
With no data to work with, you are probably going to need to use the NETWORKDAYS() function.

The syntax is:

=NETWORKDAYS(start_date , end_date , [holidays] )

Holidays is an optional list of cells that contain your holidays, IE 07/04/2020, 01/01/2020, 12/25/2020 etc. etc.

Returns the number of whole working days ( Monday thru Friday )
between two dates excluding (if provided) specified holidays.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
July 6, 2020 at 10:31:54
The NETWORKDAYS formula looks good, but when I tried entering 7/1/2020 and 7/2/2020, it still calculated 2 instead of 1. When the dates are the same, it gave me 1 instead of 0. How do I fix this?


message edited by CuriousChris


Reply ↓  Report •

#3
July 6, 2020 at 10:42:32
✔ Best Answer
With your data like:

       A          B       C       D       E       F       G
1)    SD         RD      AD      DD      DWS     DRQ     DAQ
2) 7/1/2020  7/2/2020    2                               

The formula in C2 is: =NETWORKDAYS(A2,B2)

Is this what you have?

should give me 1 day only

NETWORKDAYS() includes the Start date & End date

You will need to subtract 1 day if you want this to be 1 Day

So something like:

=NETWORKDAYS(A2,B2)-1

Will give you 1 Day for the above data. You also may need to change the Format of cell C2 to Numbers, else it may display as a Date.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
July 6, 2020 at 10:50:12
Thank's Mike. I'm good now :-)

message edited by CuriousChris


Reply ↓  Report •

Ask Question