+ Reply to Thread
Results 1 to 18 of 18

Count no. of days for the week and month from range

  1. #1
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Count no. of days for the week and month from range

    Hi I would like to count the no. of days for the week and month from data range.

    An excel file attached with necessary details.



    Pl. help providing the formula.

    Thanks,
    Nagesh.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Count no. of days for the week and month from range

    Here:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Also, since you state that 4 is your expected result then your original formula also might not work properly in some cases so you need to change red part in it too.

    Edite: Note that without ,2 it will give you result of 3 because 4th September is Sunday and it will be calculated as week 37. Therefore you must put ,2 in Weekday formula to calculate it as last day of Week 36.
    Last edited by zbor; 09-21-2016 at 03:55 AM.

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Count no. of days for the week and month from range

    Try with a Helper column

    B2=WEEKNUM(A2) and drag down.

    Now Try

    C3=SUMPRODUCT((B2:B367=C2)*(MONTH(A2:A367)=D2))
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Count no. of days for the week and month from range

    If you leave the helper columns then you just need

    Please Login or Register  to view this content.
    If someone has helped you then please add to their Reputation

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count no. of days for the week and month from range

    Try this formula. C4 in file
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Dear Mr.ZBOR,

    your formula is working fine. Thank you.

    Dear Mr. Murthy,

    Thank you very much.

    Your formula is short, simple and working but it is not working when the dates are in a different sheet and linked to the formula.

    For your ready reference, attached the excel file pl. help.

    Thanks,
    Nagesh.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count no. of days for the week and month from range

    where the formula is used. what is the actual problem. Pl mark in the file.

  8. #8
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Pl. ignore the post. new post follows.
    Last edited by nagesh.tvsr; 09-28-2016 at 06:14 AM. Reason: Error in the attachment

  9. #9
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Attachment was missing
    Last edited by nagesh.tvsr; 09-28-2016 at 05:14 AM. Reason: Attachment was missing

  10. #10
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Dear Mr. Murthy,

    That issue resolved. Thank you very much.

    I actually wanted to calculate No. of working days and working days passed (Completed) in the week (excluding holidays) as of yesterday.

    Excel file attached with necessary details.

    Pl. help.

    Thanks,
    Nagesh.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count no. of days for the week and month from range

    Thanks for the feed back.

  12. #12
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Dear Mr. Murthy,

    Pl. help on the No. of No. working days & working days passed also (pl. refer the earlier attachment).

    For you it is a very small thing and for me it is a very big thing.

    Pl. help.

    Thanks,
    Nagesh.

  13. #13
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count no. of days for the week and month from range

    I did not find in the attached file where you have used formula. Pl mark it in the file.

  14. #14
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Sir,

    Pl. find the attachment.

    Thanks,
    Nagesh.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Count no. of days for the week and month from range

    ARRAY formula in E7
    Please Login or Register  to view this content.
    ARRAY formula in E8
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 10-06-2016 at 12:33 AM. Reason: Formulas are corrected.

  16. #16
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Dear Mr. Murthy,

    Thank you so much for the solutions provided for my issue.

    One more help required on putting working days & Working days passed in a fortnight.

    Excel file attached with necessary information.

    I will be grateful if you can provide this formula too.

    Thank you,

    Nagesh.
    Attached Files Attached Files

  17. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Count no. of days for the week and month from range

    nagesh.tvsr,
    if this is new question please open new thread.

  18. #18
    Forum Contributor
    Join Date
    02-15-2012
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2019
    Posts
    425

    Re: Count no. of days for the week and month from range

    Ok. Thank you. New thread generated.

+ 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. Days of the month and week based on name month/year
    By RickMcc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-01-2016, 03:22 PM
  2. [SOLVED] Calculate Week Range from Week Number, Month and Year?
    By A108A108 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-07-2016, 03:11 PM
  3. Working days of week & month
    By nagesh.tvsr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2016, 03:35 AM
  4. Replies: 8
    Last Post: 12-04-2013, 04:14 AM
  5. [SOLVED] Count the days excluding Friday in every month using a date range.
    By miss_chloe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-23-2013, 10:42 AM
  6. How many week days in a month
    By llol_slim in forum Excel General
    Replies: 5
    Last Post: 02-08-2007, 09:01 AM
  7. [SOLVED] How can I add up lookups? Finding days in a week of a month
    By Michael at Thin Air in forum Excel General
    Replies: 5
    Last Post: 01-29-2006, 02:55 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