Md. Ahaduzzaman
Sometimes we face a deadline within which we want to finish some work. In a company providing deliveries within a fixed time is very important. Otherwise, it may cause some serious damage to the profit margin. So if we want to do some work properly within the deadline we have to first determine the due date perfectly. Here in this article, we are going to learn 7 easy and effective methods to use the due date formula in excel.
📁 Download Excel File
Download the Excel file below.
Learn to Create Due Date with Formula in Excel Using These 7 Methods
Here in this article, we will learn 7 methods to determine the due dates in Excel. Let’s assume we have a dataset of a company with its different products and production dates. We want to deliver each product after a certain time period. So in the following sections, we will describe briefly how to use the due date formula in excel to determine the due dates. The sample dataset is shown below for the demonstration.
1. By Adding Date
Here we will learn how to determine the due date in Excel by adding 2 values. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we go to the cell E6 and write this formula in the formula box.
=C6+D6
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we will get the due dates in column E.
2. Using Date Function
In this section, we will learn how to use the DATE function to use the due date formula in excel. Therefore the breakdown steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell F6, we write this formula in the formula box. Here C6 is our year, D6 is the month and E6 is the day of delivery.
=DATE(C6,D6,E6)
- Following that we use the Fill Handle tool to drag the formula.
- Finally, we will get the due date in column F.
3. Applying Conditional Formatting
We will learn in this method how to apply Conditional Formatting properly to use the due date formula in excel. So the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we select our data range in column E.
- Following that, we go to the Conditional Formatting option.
- After that, we select the New Rule option.
- We will get a dialogue box here. We select the Rule type shown below.
- Then we select less than or equal to from the dropbox.
- We write this formula in the formula box.
=TODAY()
- After that, we select the Format option.
- We get another dialogue box. Here we select the Fill option first.
- After that, we chose a background color.
- Then we press OK.
- We will find the result in column E.
- The products that have been already delivered are shown with blue filling.
4. Using IF Function
In this section, we will see how to use the IF function to use the due date formula in excel. Therefore the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly we write this formula in the formula box of cell F6.
=IF(E6<=TODAY(),”Done”,”Not Done”)
🔨 Formula Breakdown
👉 Here E6<=TODAY() is our logical test where E6 cell value is checked with the date of today.
👉 If the value is less or equal it will show “Done”.
👉 Otherwise it will show “Not Done”.
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get the result in column F.
5. Applying EDATE Function
Here we will learn how to apply the EDATE function to use the due date formula in excel. Hence the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell E6, we write this formula in the formula box.
=EDATE(C6,D6)
- Here C6 is the production date and D6 is the delivery month from the production date.
- After that, we use the Fill Handle tool to drag the formula.
- Finally, we get the due dates in column E.
6. Using Both EDATE & YEARFRAC Functions
We will learn in this section how to use the EDATE & YEARFRAC functions together to use the due date formula in excel. So the step-by-step procedures are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell D6, we write this formula in the formula box.
=EDATE(C6,60*12)
- Here C6 is the start date and 60*12 denotes the retirement age in months.
- In cell E6 we write this formula in the formula box.
=YEARFRAC(C6,D6)
- Here C6 is the start date and D6 denotes the end date.
- It returns us the retirement age.
- Now we use the Fill Handle tool to drag the formula in both columns D & E.
7. Applying WORKDAY Function
In our final method here we will see the use of the WORKDAY function to use the due date formula in Excel. Therefore the steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell E6, we write this formula in the formula box.
=WORKDAY(C6,D6)
- Here C6 is the production date and D6 is the delivery days excluding the weekend and holidays.
- Then we use the Fill Handle tool to drag the formula.
- Finally, we get the due dates in column E.
How to Create Due Date Reminder with Formula in Excel
Here we will learn how to use the IF, AND & TODAY functions together to create a due date reminder formula. The steps are as follows.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly in cell C6, we write this formula in the formula box:
=IF(AND(E6<>””,TODAY()+$C$13>=E6),”Yes”,”No”)
🔨 Formula Breakdown
👉 Here AND(E6<>””,TODAY()+$C$13>=E6 is our logical test. We check 7 days ahead of the due date whether it has passed or not.
👉 If it passes the cell will show “YES”.
👉 Otherwise the cell will show “No”.
- Following that we use the Fill Handle tool to drag the formula.
- Finally, we will get the reminder in column F.
📄 Important Notes
🖊️ When we use Excel functions we have to be careful about the function arguments.
🖊️ WORKDAY function excludes holidays and weekends while calculating the due date.
📝 Takeaways from This Article
📌 We can find the due date by simply adding two values.
📌 We can use the DATE function to determine the due date formula in Excel.
📌 Applying Conditional Formatting can be a way also.
📌 We can use the IF function to determine the due date formula in Excel.
📌 EDATE function is another way to determine the due date formula in Excel.
📌 If we want we can use the EDATE & YEARFRAC functions together.
📌 Apart from all these options WORKDAY function can be another way.
Conclusion
Here in this article, we have learned 7 different ways to use the due date formula in Excel. We can determine it by simply adding or using Excel functions like DATE, IF, EDATE, YEARFRAC, WORKDAY, etc. Users can use any of these functions according to their liking. Also, you can comment in the comment section if you have any queries. For more Excel-related solutions and queries visit our website Excelden.
