locked
Как в диапазоне даты вычислить выходные дни. RRS feed

  • Question

  • Здравствуйте дорогие друзья ? У меня есть список отгулов. есть два поля "начало" "конец". Как мне вычислить сколько выходных дней между "начало" и "конец" ?
    Tuesday, January 16, 2018 12:13 PM

Answers

  • Hi,

    You can use the following formula:

    =(([End Date]-MOD(WEEKDAY([End Date])-1,7)-[Start Date]-MOD(1-WEEKDAY([Start Date])+7,7))/7)+1

    Note: you need to change [Start Date] and [End Date] to your columns.

    The result is:

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Marked as answer by Arafat05ru Wednesday, January 17, 2018 10:32 AM
    Wednesday, January 17, 2018 8:20 AM

All replies

  • Hi,

    The language of the forum is English, please translate your issue to English.

    Thanks,

    Wendy 


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, January 17, 2018 2:08 AM
  • Hi,

    I translated your issue using Google Translate, it was like:

    It seems that you want to calculated the days between two date columns. You could create a Calculated column, then user the following formula:

    =DATEDIF([Column1], [Column2],"d")

    More information about Calculated Column, you can refer to:

    https://msdn.microsoft.com/en-us/library/bb862071.aspx#Date and time formulas

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Wednesday, January 17, 2018 3:06 AM
  • Thanks for the tip Wendy , but I need to calculate the number of Sundays between two dates. For example, 10.01.2018 for 25.01.2018 ? How to do it ?
    Wednesday, January 17, 2018 8:04 AM
  • Hi,

    You can use the following formula:

    =(([End Date]-MOD(WEEKDAY([End Date])-1,7)-[Start Date]-MOD(1-WEEKDAY([Start Date])+7,7))/7)+1

    Note: you need to change [Start Date] and [End Date] to your columns.

    The result is:

    Thanks,

    Wendy


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    • Marked as answer by Arafat05ru Wednesday, January 17, 2018 10:32 AM
    Wednesday, January 17, 2018 8:20 AM
  • Thanks for the help.
    Wednesday, January 17, 2018 10:33 AM
  • How to calculate the Sunday and Saturday ? separately or Saturday ?
    Wednesday, January 17, 2018 10:36 AM
  • I understand. should that be multiplied by 2

    =((([Срок]-ОСТАТ(ДЕНЬНЕД([Срок])-1;7)-[Дата начала]-ОСТАТ(1-ДЕНЬНЕД([Дата начала])+7;7))/7)+1)*2

    Wednesday, January 17, 2018 10:50 AM