Forum Discussion

mike50's avatar
mike50
Copper Contributor
Mar 27, 2020

Creating a Spreadsheet for Deadlines

Hi,   I am trying to create a spreadsheet for various deadlines and could use some help.  What I want is to be able to enter a date, say March 27, 2020, and then have Excel populate deadlines assoc...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 27, 2020

mike50 

You may use WORKDAY() or WORKDAY.INTL() for that. 

By the way, =SUM(A2,30) is equivalent of =A2+30

mike50's avatar
mike50
Copper Contributor
Mar 29, 2020

don’t those functions just count business days instead of calendar days? I.e if I want to add 30 business days instead of 30 calendar days?  What I want is a little different. I want to add calendar days but have special rules for when start date or finish date is non-business day.  How can I use the WORKDAY fx to accomplish this? Thanks. SergeiBaklan 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 29, 2020

    mike50 

    for the Start date the could be

    =IF(WORKDAY(A2-1,1)=A2,A2,WORKDAY(A2,1))

    End date

    =IF(WORKDAY(IF(WORKDAY(A2-1,1)=A2,A2,WORKDAY(A2,1))+29,1)=
       IF(WORKDAY(A2-1,1)=A2,A2,WORKDAY(A2,1))+30,
       IF(WORKDAY(A2-1,1)=A2,A2,WORKDAY(A2,1))+30,
       WORKDAY(IF(WORKDAY(A2-1,1)=A2,A2,WORKDAY(A2,1))+30,1)
)

Resources