Excel Workday function: Find end date - exclude weekends & holidays

This video demonstrates how to efficiently create timelines and timetables in Excel, particularly useful for project management and finance reporting.

🛠 Key Feature: The Workday Function
- Functionality: Calculates end dates based on a start date and the number of working days, excluding weekends and holidays.
- Usage Scenarios: Ideal for project time plans or finance reporting timetables.

📅 Examples Illustrated:
- Project Management: For a task starting on a specific date, the video shows how to calculate the completion date considering working days, weekends, and holidays.
- Finance Reporting: Demonstrates setting up monthly reporting deadlines based on working days of each month.

👩‍💻 Steps Highlighted:
- Input Start Date and Working Days: Input the task's start date and the number of required working days.
- Apply Workday Function: Use Excel's 'Workday' function to compute the end date, excluding weekends and holidays.
- Consideration of Weekends: The standard Workday function assumes weekends are Saturday and Sunday, but 'Workday International' allows customization of weekend days.
- Practical Usage: The video includes a calendar to validate the function's accuracy and an example of custom formatting to display only the month and year in a cell while retaining the complete date.

🔍 Additional Features:
- Auto-Filling Dates: Shows how to drag the formula across other cells for automating date calculations for multiple tasks.
- Customization for Local Holidays: Includes how to incorporate local holidays into the calculation for more precise planning.
- Flexibility for Different Scenarios: Adapts to various project and reporting needs by altering the start dates and working days.

Excel's workday function is great when you need to calculate an end date based on a given number of working days - which means you need to exclude weekends and holidays. Some examples are
- creating a project timeline
- creating a reporting timetable
In this video I show you both examples.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[michaeldiamond]

Great video Leila. that was a nice little twist when you talked about the formatting and showed us how to manipulate the actual start date in the formula.

[carlos]

Thanks to your informative video, it made my work easy for creating day to day schedule with drafting works

[gpi]

"This is a weekend and a holiday. It's very unfortunate" :)

[divyanshusaxena]

Hi Leila, Could you pls make a video on how to calculate estimated end time for each task, if we have start time and duration in hours ?

[gopalakrishnarao]

Mesmerizing explanation with working days formula. Happy to view your videos. Doing a very noble job for the excel lovers/addicts/users. Thanks cannot be measured in terms of words.

[prayagkudalkar]

Hi teaching is commendable. Thanks for your teachings. What if someone has holiday on every Sunday but alternate Saturday.

[philipmcdonnell]

Hi Leila, many thanks for the video, it got me a long way along the tortuous route I have to take get my forecasting correct.  One thing: in construction, the start date is usually counted as a work day which Excel miscalculates unless the first calculated date is based upon Start Date-1.

[bakirkr]

thank you very much this video help me a lot

[abdanomer]

Thank you..

Simple lesson with very direct example. Any one would really understand this function easily by this video.

This is very helpful for projects scheduling and detailed planning.

Great effort ..

I have two questions for you if you can help!

1. How to get the start of the month or end of the month for each data in a list?

2. How can i use the excel as an alarm (using what ever VBA or functions ) for a certain date to remind for something (like cheque date, meeting etc) when i open my pc - if possible- or open any excel sheet ?

[thairtalib]

Whenever I miss the perfect of something, I go to leila to catch the perfect, thanks to the lady of perfect .

[mariaaissalagumbay]

Hi thank you for this...and also if you have easy way to track leave of the staffs...thank you

[SanjayKumar-yxgc]

Great explanation. Thanks you so much Leila.

[sachinrv]

Always good to watch your videos. They are so informatively explained. :)

[jerrydellasala]

I enjoy your videos and watch them if YT puts them on the home page which is why I'm writing almost 4 years later. In the first example suppose the Estimated Data was due on the first of the month, so you put 0 in the Due On Working Day column. By subtracting 1 from the start date, the due date becomes the day before. If you remove the subtraction, May and June work fine, however April 1 is a Saturday, and October 1 is a Sunday, so I would expect WORKDAY to take this account and return 4/3/2017 and 10/2/2017, however they both return the first of the month. Changing the 0 working days to 1, they correctly return 4/3 and 10/2. The reason I saw this was because I was thinking that the start date itself should be wrapped in the WORKDAY function with 0 days as the days parameter to compensate for months that start on the weekend. This also happens with the .INTL version. Your thoughts?

[piusthomas]

Hi Leila
Could you explain how to auto fill last working of the month

[safeeras]

For a list of employees, want to calculate no: of days they worked in a year starting with date of increment due date to current month. Increment beginning date is different for all employees and we are provided with the attendances of all those employees of a one or two years or more. From that range of attendance, want to calculate only the eligible days from the date of increment date to the current month

[yonasmisganaw]

thanks and this is the best thing i have learned

[ucirlos]

Thanks, this is great! Can you show how to include certain Saturdays that will count as workdays?

[Someonereallycares]

What if you have an end date and number of work day and need to know when the date in the past it should start?

[mahamahmoud]

we do substantially appreciate your customary support, uhmm could you plz let us know how to calculate end date with time and date if for example i want to add 3 working-days to 7/1/2020 2:35 PM getting exact end date and time, much love :)