Forum Discussion

Frederick_Asare's avatar
Frederick_Asare
Copper Contributor
Nov 14, 2023

Finding Future Dates

Greetings to you all Excel Nerds.

I have in column A start dates in date time format(dd-mm-yyyy h:mm AM/PM) and in Column B duration(in hours). I am struggling to write a formula that returns the end date, taking out weekends and holidays. A typical shift day is 8 hours long excluding breaks. For example if A1 = 14/11/2023 7:00 am and B1 = 24, the output should be 16/11/2023 3:00 pm. Any help will be greatly appreciated.🙏

excel
Formulas and Functions

5 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 14, 2023

    Frederick_Asare 

    To calculate the end date and time while excluding weekends and holidays in Excel, you can use a combination of functions, including WORKDAY for excluding weekends and a custom formula or approach for excluding holidays. If you have a list of holidays, you could use the NETWORKDAYS function to subtract the number of holidays.

    Here's a step-by-step guide:

    Assuming your start date is in cell A1, duration in B1, and you have a list of holidays in a separate range (let's say H1:H10), you can use the following formula for the end date:

    =WORKDAY(A1, INT((B1+TIME(8,0,0)-1)/8) + IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0), H1:H10) + MOD((B1+TIME(8,0,0)-1),8)/24

    This formula works as follows:

    1. WORKDAY(A1, ...) calculates the end date excluding weekends.
    2. INT((B1+TIME(8,0,0)-1)/8) calculates the number of full working days needed (excluding weekends).
    3. IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0) checks if there are any remaining hours after the full working days and adds an extra day if needed.
    4. MOD((B1+TIME(8,0,0)-1),8)/24 calculates the remaining hours and converts them to days.

    Please note that this formula doesn't account for holidays. If you have a list of holidays in cells H1:H10, you can use the NETWORKDAYS function to subtract the number of holidays:

    =WORKDAY(A1, INT((B1+TIME(8,0,0)-1)/8) + IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0), H1:H10) + MOD((B1+TIME(8,0,0)-1),8)/24 - NETWORKDAYS(A1, WORKDAY(A1, INT((B1+TIME(8,0,0)-1)/8) + IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0), H1:H10), H1:H10) / 24

    Remember to adjust the range H1:H10 to match the actual range of your holiday dates.

    This formula should give you the end date and time, excluding weekends and holidays, based on the provided start date and duration. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

    • Frederick_Asare's avatar
      Frederick_Asare
      Copper Contributor
      Nov 14, 2023
      NikolinoDE Thanks for your help. The formula you suggested accurately gives the end date but
      time is not accurate. At least I now know a combination of FUNCTIONS that could be used to achieve the result. I would continue to tweak the formula to see if works as expected.
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Nov 14, 2023

        Frederick_Asare 

        If the time component is not accurate, we need to adjust the formula to include the correct time calculation. Here's an updated formula:

        =WORKDAY(A1, INT((B1+TIME(8,0,0)-1)/8) + IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0), H1:H10) + MOD((B1+TIME(8,0,0)-1),8)/24 - NETWORKDAYS(A1, WORKDAY(A1, INT((B1+TIME(8,0,0)-1)/8) + IF(MOD((B1+TIME(8,0,0)-1),8)>0,1,0), H1:H10), H1:H10) / 24 + MOD(A1,1)

        This modification includes MOD(A1,1) at the end to consider the time portion of the start date. This should provide a more accurate end date and time based on the given start date and duration while excluding weekends and holidays.

        Make sure to replace the formula in your Excel sheet with this updated version.

Resources