none
calculate work days between two dates for calculated columns RRS feed

All replies

  • If you have Excel 2010 or later:

    =NETWORKDAYS.INTL(A1,B1,7)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by David_JunFeng Sunday, January 31, 2016 2:48 PM
    • Unmarked as answer by mafab Monday, February 1, 2016 5:28 AM
    Sunday, January 17, 2016 8:26 PM
  • Hi,
    In calculated column, this formula don't work , so looking to find a solution something like above shown formula , it works with Sun,Mon as weekend , I am looking how to modify it for Fri,Sat as weekend
    Monday, January 18, 2016 3:30 AM
  • >>>In calculated column, this formula don't work , so looking to find a solution something like above shown formula , it works with Sun,Mon as weekend , I am looking how to modify it for Fri,Sat as weekend

    According to your description, Hans Vogelaar has provided good solution, the NETWORKDAYS.INTL function syntax has the following arguments:

    Start_date and end_date    Required.
    The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.

    Weekend    Optional.
    Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.

    Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

    For example, 0000011 would result in a weekend that is Saturday and Sunday.

    For more information, click here to refer about NETWORKDAYS.INTL function

    • Marked as answer by David_JunFeng Sunday, January 31, 2016 2:48 PM
    • Unmarked as answer by mafab Monday, February 1, 2016 5:28 AM
    Monday, January 18, 2016 6:00 AM
  • i GOT solution - but if say both dates (start/end) are on same day- then #REF! error

    =IF(AND((WEEKDAY(B3,15))<(WEEKDAY(A3,15)),((WEEKDAY(A3,15))-(WEEKDAY(B3,15)))>1),(((DATEDIF(A3,B3,"D")+1))-(FLOOR((DATEDIF(A3,B3,"D")+1)/7,1)*2)-2),C6(((DATEDIF(A3,B3,"D")+1))-(FLOOR((DATEDIF(A3,B3,"D")+1)/7,1)*15)))

    can suggest on this
    Tuesday, January 19, 2016 9:47 PM
  • >>>i GOT solution - but if say both dates (start/end) are on same day- then #REF! error

    According to your description, invalid cell reference errors occur when a spreadsheet formula contains incorrect cell references. This happens most often when:
    1.Columns or rows containing data used in the formula are accidentally deleted.
    2.A formula has been moved or copied to another cell and the cell references are incorrect.
    3.Data used in a formula has been moved, leaving the function or formula with incorrect cell references

    So I suggest that if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then

    click the resolution that is appropriate for your data.

    For more information, click here to refer about Correct a #REF! error

    Wednesday, January 20, 2016 7:41 AM
  • I will suggest some workaround as a solution combining with excel- as presently standard list and its calculated column are not capable to give a solution itself, please wait for a while

    Monday, February 1, 2016 5:30 AM