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 associated with that date.   I understand I can generally accomplish this by formatting the cells to dates and then use the sum function [=SUM(A2, 30)] to get a deadline based upon a certain number of days from the starting date you type in.  But what I don't know is how to add rules to account for non-business days. 

 

For example, if the starting date is a Saturday, I want to start counting days on the following working day.  Similarly, if a deadline falls on a weekend or holiday, the deadline that pops up should be the following working day.  Is there an easy way to do this in Excel?  tyia for your help!

excel
Formulas and Functions

5 Replies

Sort By
  • 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