Calculate Holidays with Networkdays

  • Hi Guys,
    I have a simple excel workbook, where cell values are being populated through another excel workbook's VBA modules. The values are simple times/days in format ddd [$-409]m/d/yy h:mm AM/PM;@ - start times, and end times.


    In the attached Excel file, there is a Sheet for Fitter2 (in a workshop).
    In Fitter2 sheet, job Start1 time is in Cell # E3, job Pause1 time is in Cell # F3.
    I want to calculate the passed time in hours:minutes:seconds between these two dates.


    FitterStatus sheet has the starting time of Fitter2, ending time of Fitter2, and his holiday plans. all these are named ranges, as F2ST, F2FT, HolidayList.


    it is not working for some reason, and constantly giving me "#Value" error.


    the formula i am trying to use is (used in F3)


    =IF(OR(F2FT<F2ST,End_Date<Start_Date),0,(NETWORKDAYS(Start_Date,End_Date,HolidayList)-(NETWORKDAYS(Start_Date,Start_Date,HolidayList)*IF(MOD(Start_Date,1)>F2FT,1, (MAX(F2ST,MOD(Start_Date,1))-F2ST)/(F2FT-F2ST)))-(NETWORKDAYS(End_Date,End_Date,HolidayList)*IF(MOD(End_Date,1)<F2ST,1,(F2FT-MIN(F2FT,MOD(End_Date,1)))/(F2FT-F2ST))))*(F2FT-F2ST)*24)



    Please help.

  • Re: Calculate Holidays with Networkdays


    Its because your dates in column E and F are not being stored as valid Excel dates


    For example
    =NETWORKDAYS(DATEVALUE(E3),DATEVALUE(F3),HolidayList)
    is failing because E3 and F3 are strings (which look like dates) but Excel cant understand and convert them to dates.


    I tried =NETWORKDAYS(DATEVALUE(E3),DATEVALUE(F3),HolidayList) but this didnt work either...


    So you will need to do some work on Columns E and F to convert them into proper real dates that Excel can understand


    Notice the difference between the dates in your HolidayList named range and the dates in Columns E and F. Right click on both sets of dates and click "format cells". The dates in the HolidayList will be "Date" format, the dates in the columns E & F will be "General" format.


    If you need a solution to convert your string literal dates into real excel dates, maybe start a new thread on that with an accurate thread title.


    Regards,
    Ger


    NB, I moved your post out of the Tips and tricks forum and put it in here, and I also renamed your thread title. Welcome to the forum.

    Check out our new reputation system. Click on the Like button under the post!
    _______________________________________________

    [SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...


    The BEST Lookup function of all time


    Dynamic Named Ranges are your bestest friend[/SIZE]
    _______________________________________________