+ Reply to Thread
Results 1 to 11 of 11

Monthly Summary Based on Weekly Dates

  1. 09-12-2012, 02:47 AM #1
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Monthly Summary Based on Weekly Dates

    Is there any macro or function that can calculate which weeks belongs to a month range?

    Please see the attached file for the details.

    Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 09-12-2012, 03:01 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Monthly Summary Based on Weekly Dates

    One way:

    C10: =SUMPRODUCT(--(LEFT($C$2:$I$2,3)=C$9),--($C$3:$I$3)) and drag across

    Note that your example is wrong as you only have one column with June dates.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 09-12-2012, 03:08 AM #3
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Monthly Summary Based on Weekly Dates

    Gee Thanks!

    That was what i'm looking for.

    Sorry for the example, I haven't change the month name for june.

    Thumbs up!
    Reply With QuoteRegister To Reply
  4. 09-12-2012, 03:18 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Monthly Summary Based on Weekly Dates

    You're welcome. Thanks for the rep.

    Just wanted to point it out in case you thought the formula didn't work properly in some cases.
    Reply With QuoteRegister To Reply
  5. 09-13-2012, 05:47 AM #5
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Monthly Summary Based on Weekly Dates

    @TMS

    is it possible that the array for the weekly cells is from other worksheet?
    Reply With QuoteRegister To Reply
  6. 09-13-2012, 06:03 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Monthly Summary Based on Weekly Dates

    Like:

    =SUMPRODUCT(--(LEFT('Other Sheet'!$C$2:$I$2,3)='This Sheet'!C$9),--('Other Sheet'!$C$3:$I$3))

    maybe?

    Regards, TMS
    Reply With QuoteRegister To Reply
  7. 09-13-2012, 06:34 AM #7
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Monthly Summary Based on Weekly Dates

    @TMS

    Yes it is working.

    I've also try it on a simple way and it's ok.

    It's just that i use many variables(for the ranges) that's why it makes me dizzy a while ago but now is ok.

    Thanks,

    thisisgerald
    Reply With QuoteRegister To Reply
  8. 09-13-2012, 07:04 AM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Monthly Summary Based on Weekly Dates

    You're welcome.
    Reply With QuoteRegister To Reply
  9. 09-14-2012, 04:12 AM #9
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Monthly Summary Based on Weekly Dates

    @TMS

    Sir, another question. How about if there will be their own calendar for the computation? How can i compute the output base on their monthly calendar?

    Please see the attached file for the details.

    Thanks in advance!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 09-14-2012, 04:22 AM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,380

    Re: Monthly Summary Based on Weekly Dates

    I think it's time for a new thread. New question, new thread ... link back to this thread to provide background information if you feel it is appropriate.

    A new thread will get more input, particularly as this thread is already marked solved.

    Regards, TMS
    Reply With QuoteRegister To Reply
  11. 09-14-2012, 04:49 AM #11
    thisisgerald
    thisisgerald is offline
    Forum Contributor
    Join Date
    09-05-2012
    Location
    It's more fun in the Philippines :D
    MS-Off Ver
    Excel 2007
    Posts
    209

    Re: Monthly Summary Based on Weekly Dates

    Ok. Thanks! :D
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1