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 en...
excel
Formulas and Functions
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.

    • Frederick_Asare's avatar
      Frederick_Asare
      Copper Contributor
      Nov 17, 2023

      NikolinoDE Thanks for your assistance. I am not sure if it's my Windows time setting but the end time still doesn't work out as expected. For now, I will go with the end date which seems to be working fine so far. 

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Nov 17, 2023

        Frederick_Asare 

        Wouldn't this work?

        =WORKDAY(INT(A1),B1/8,Table1[Holidays])-1+MOD(A1,1)+8/24

        Create a table somewhere called Table1 with one column "Holidays".

        See attached file.

         

         

         

Resources