+ Reply to Thread
Results 1 to 17 of 17

Monthly to Cusotm Date Report

  1. #1
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Monthly to Cusotm Date Report

    Hi Experts,
    I hoper every one is well, I have a file which is created report my month but I need it will change from monthle to custome date (example start date 09/13/20 End Date 09/27/20 etc), I'm using formula in A11 ={IF(ROWS(A$11:A11)>$B$2,"",INDEX(Table9[[#All],[Column1]],SMALL(IF(TEXT(Table9[[#All],[Column1]],"yyyymmm")=$A$2&$A$3,ROW(Table9[[#All],[Column1]])-ROW(DataFeeding!$A$4)+1),ROWS(A$11:A11))))}

    Thanks in Advance,

    Regards,
    Aaru
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Monthly to Cusotm Date Report

    Which version of Excel are you using currently?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Monthly to Cusotm Date Report

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn



  4. #4
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    Hi AliGW, I'm using MS Excel 2007

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Monthly to Cusotm Date Report

    perhaps

    =IF(ROWS(A$11:A11)>$C$2,"",INDEX(Table9[[#All],[Column1]],SMALL(IF((Table9[[#All],[Column1]]>=$B$2)*(Table9[[#All],[Column1]]<=$B$3),ROW(Table9[[#All],[Column1]])-ROW(DataFeeding!$A$4)+1),ROWS(A$11:A11))))}

    where b2/b3 are Start/End dates, C2 -No of entries

  6. #6
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    Thanks, John, I have applied but it is not working

  7. #7
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    I have attached excel file formula is not working
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,487

    Re: Monthly to Cusotm Date Report

    Explain what you mean by "not working". Wrong result? Error message? What?

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Monthly to Cusotm Date Report

    Change C2

    =SUMPRODUCT((Table9[[#All],[Column1]]>=$B$2)*(Table9[[#All],[Column1]]<=$B$3))

    It does work!

  10. #10
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    Thanks john, I'm talking about A11 which is an update by month, I want it will update by custom date, I have sent sample file.

  11. #11
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    I have sent sample file

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,397

    Re: Monthly to Cusotm Date Report

    I KNOW that! If check the formula: it uses C2 which was incorrect. Why do you think I gave you the new formual?

    PLEASE test before saying they do not work!

  13. #13
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    I have tried it is working on C3 but, the below report formulas is showing #num.
    Attached Images Attached Images

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Monthly to Cusotm Date Report

    Please stop posting pictures. We cannot diagnose a jpg. Post the file.

  15. #15
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    ok SIR.........

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

    Re: Monthly to Cusotm Date Report

    See attached -not all completed
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    01-23-2016
    Location
    karachi
    MS-Off Ver
    2007
    Posts
    316

    Re: Monthly to Cusotm Date Report

    Thank You John, you are excellent.

+ 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: 9
    Last Post: 06-11-2018, 07:17 AM
  2. [SOLVED] Report Monthly-Date to Date Formula mod
    By n4t4nes in forum Excel General
    Replies: 2
    Last Post: 06-11-2018, 03:55 AM
  3. How to extratc data as weekly report and monthly report
    By Nisar.mohammed in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2014, 11:19 AM
  4. Monthly, Bi-monthly, Quarterly, Yearly Report Tracking Help
    By eugene_lys in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-26-2014, 11:08 PM
  5. Replies: 1
    Last Post: 08-15-2013, 02:15 PM
  6. How to collect daily report from spread sheet and accumulate for monthly report
    By yshguru in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2013, 05:17 PM
  7. Need to save Running Total and generate Weekly and Monthly Report based on date
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-04-2012, 09:10 AM

Tags for this Thread

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