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
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