+ Reply to Thread
Results 1 to 6 of 6

Calculate number of months from date excluding Sundays

  1. #1
    Registered User
    Join Date
    05-10-2017
    Location
    Leeds, UK
    MS-Off Ver
    excel 2013
    Posts
    12

    Calculate number of months from date excluding Sundays

    Good afternoon

    Is it possible to calculate 6 months from a date excluding Sundays?

    I have used the =EDATE(A1,6) function to return 6 months from the date with no problem, however can I add in for the function to exclude Sundays?

    Thank you.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,181

    Re: Calculate number of months from date excluding Sundays

    Not sure what excluding Sundays means. If the start date is Monday, April 3, 2017, one month later is May 3, 2017 (Tuesday) whether you exclude Sundays or not. Are you saying if the result is a Sunday, then make it a Monday instead?
    Do you want to count 30 days between dates but don't count Sundays? Please clarify.
    ChemistB
    My 2¢

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    05-10-2017
    Location
    Leeds, UK
    MS-Off Ver
    excel 2013
    Posts
    12

    Re: Calculate number of months from date excluding Sundays

    Good morning

    It is for a pay calculation, so in my organisation Sundays are not counted as a working day (don't ask me why!).

    I suppose what I would like is to calculate exactly 6 months (in calendar days) from a date, then subtract any day that is a Sunday within that 6 month period.

    Thank you.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Calculate number of months from date excluding Sundays

    This will give you number of working days in 6 month calendar period excluding Sundays:

    =NETWORKDAYS.INTL(A1,EDATE(A1,6),11)

    A1= Start date

    Does this help?

  5. #5
    Registered User
    Join Date
    05-10-2017
    Location
    Leeds, UK
    MS-Off Ver
    excel 2013
    Posts
    12

    Re: Calculate number of months from date excluding Sundays

    Hi John

    Thank you for the reply. Your formula above is great however it is not producing the date I would expect. I suspect I haven't explained myself properly so if I could clarify a little further.

    Our organisation gives 156 days sick pay entitlement. So if an individual goes sick on the 1/1/17, I need excel to calculate 156 days from 1/1/17 to give me the date their pay should drop, but 'skip' Sundays. So 1/1/17 +156 = 6/6/17, however I would expect the date the pay drops to be around the end of June if Sundays are not included in the calculation.

    Thanks again.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Calculate number of months from date excluding Sundays

    Try

    =WORKDAY.INTL(A1,156,11)

    A1= 01/01/2017

    Result is 01/07/2017

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 04-27-2015, 06:33 PM
  2. How to calculate number of months by date range for a specific year?
    By redstyles in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2015, 05:53 PM
  3. To calculate number of days excluding sundays
    By palani_k15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2014, 09:02 AM
  4. Replies: 5
    Last Post: 12-11-2012, 01:20 AM
  5. Replies: 1
    Last Post: 08-22-2012, 10:38 AM
  6. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  7. Replies: 1
    Last Post: 11-01-2005, 04:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1