+ Reply to Thread
Results 1 to 17 of 17

Daily report count to be captured in the date wise

  1. 05-23-2016, 03:31 AM #1
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Daily report count to be captured in the date wise

    Hi Experts,

    Could you please suggest me how can i capture the excel sheet in the daily report date wise Open and resolved count.for example please see the table below, the raw data for open count is available "Sheet1" and Resolved is "Sheet2" .

    5/23/2016 5/24/2016 5/25/2016
    Account Open Resolved Open Resolved Open Resolved
    Infosys 0 1 32 4 2 0
    KPMG 0 1 0 0 0 0
    CSC 0 0 0 0 0 0
    Manpower 0 0 0 0 0 0
    Anthem 0 0 0 0 0 0
    Philips 0 0 0 2 0 0
    Xerox 0 0 1 0 0 0

    it's need to be captured date wise, PS:the report name should be like "Daily Report for 23rd May 2016" and Daily Report for 24th May 2016".

    Thanks
    Raju
    Reply With QuoteRegister To Reply
  2. 05-23-2016, 03:51 AM #2
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Hi rajuganapathy,
    Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
    -If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".

    -Always upload a workbook before start your question
    To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

    +++ If my answer(s) helped you, please add me reputation by click on * +++
    Reply With QuoteRegister To Reply
  3. 05-23-2016, 10:26 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Daily report count to be captured in the date wise

    Often a copy/paste to here does not come through as intended - yours is 1 of those times

    If you need to count by date and something else, take a look at the COUNTIFS() function....

    =COOUNTIFS(date-range, "date", something-else-range, "something else")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 05-24-2016, 03:25 AM #4
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    How to upload the files please assist
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 05-24-2016, 03:27 AM #5
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    Done Please see the file and suggest me, PS the file name will be differ one file is hold only one day data only.

    Regards
    Raju
    Reply With QuoteRegister To Reply
  6. 05-24-2016, 04:53 AM #6
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    You can do like this

    D6 =SUMPRODUCT(('Raw Data-Open Tickets'!$A$5:$A$347=C6)*(YEAR('Raw Data-Open Tickets'!$H$5:$H$347)=YEAR(D$4))*(MONTH('Raw Data-Open Tickets'!$H$5:$H$347)=MONTH(D$4))*(DAY('Raw Data-Open Tickets'!$H$5:$H$347)=DAY(D$4)))
    Reply With QuoteRegister To Reply
  7. 05-25-2016, 03:41 AM #7
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    This formula is working only the Open incidents, when i have try to the same in Resolved Tickets it's showing error message.here you using the column "H", for day wise data capturing can you please suggest data capturing via file name like "Daily report 23rd May 2016".

    Thanks
    Raju
    Reply With QuoteRegister To Reply
  8. 05-25-2016, 05:40 AM #8
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Can you upload the file with those "ERROR" message. Thanks
    Reply With QuoteRegister To Reply
  9. 05-25-2016, 05:55 AM #9
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    Please see the error message.
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  10. 05-25-2016, 06:01 AM #10
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Please upload excel file, so i can verify the data and do the testing.
    Reply With QuoteRegister To Reply
  11. 05-26-2016, 03:23 AM #11
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    Refer the excel sheet
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 05-26-2016, 03:35 AM #12
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    =SUMPRODUCT(('Raw Data-Resolved Tickets'!$A$5:$A$347=C6)*(YEAR('Raw Data-Resolved Tickets'!$H$5:$H$347)=YEAR(D$4))*(MONTH('Raw Data-Resolved Tickets'!$H$5:$H$347)=MONTH(D$4))*(DAY('Raw Data-Resolved Tickets'!$H$5:$H$347)=DAY(D$4))) <--- wrong, because of A$5; $H$5

    =SUMPRODUCT(('Raw Data-Resolved Tickets'!$A$6:$A$347=C6)*(YEAR('Raw Data-Resolved Tickets'!$H$6:$H$347)=YEAR(D$4))*(MONTH('Raw Data-Resolved Tickets'!$H$6:$H$347)=MONTH(D$4))*(DAY('Raw Data-Resolved Tickets'!$H$6:$H$347)=DAY(D$4))) <--- Please use this.

    Your data start at row 6, but formula start with row 5.
    Reply With QuoteRegister To Reply
  13. 05-26-2016, 04:00 AM #13
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    Hi Wenqq3,

    Thanks it's working fine, but mu question is how can i capture the date wise using formula. cause the daily report hold one day data only when i copy paste and update the another day report. how it's capture the data in based on the file name.

    23 rd report is "Daily report for 23 rd May 2016" and the 24 day is "Daily report for 24th May 2016" based on this how it's capture the value.

    hope that clarifies.

    Thanks
    Raju
    Reply With QuoteRegister To Reply
  14. 05-26-2016, 04:14 AM #14
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Formula is real time update, based on the worksheet "Raw Data-Open Tickets" and "Raw Data-Resolved Tickets".
    My suggestion:
    Option A
    1. You keep everyday report into the same workbook, and your worksheet will be "Raw Data-Open Tickets#23May2016", "Raw Data-Open Tickets#24May2016",
    2. Then the formula need to change

    Option B
    1. Once you done the daily report and get the value in the worksheet "Data Value"
    2. You need do copy and paste as value, to store the final value on that day.
    3. So that when you do another day report, the previous day result wont get update.

    Option C
    1. Write VBA code.
    2. It can do like, specific the report date you want.
    3. Code will import the specific date report.
    4. And do the Formula and insert the final value in "Data Value".
    Reply With QuoteRegister To Reply
  15. 05-26-2016, 05:51 AM #15
    rajuganapathy
    rajuganapathy is offline
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Daily report count to be captured in the date wise

    Hi Please share the VBA code
    Reply With QuoteRegister To Reply
  16. 05-27-2016, 02:55 AM #16
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Please find attachment.
    You need specific the file name, and the date. And the file must put together with this "Report Data Value.xls". Then click RUN ME
    Attached Files Attached Files
    Last edited by wenqq3; 05-27-2016 at 03:04 AM.
    Reply With QuoteRegister To Reply
  17. 05-27-2016, 03:05 AM #17
    wenqq3
    wenqq3 is offline
    Valued Forum Contributor wenqq3's Avatar
    Join Date
    04-01-2013
    Location
    Malaysia
    MS-Off Ver
    Excel 2003
    Posts
    868

    Re: Daily report count to be captured in the date wise

    Due to your file have a lots of hidden sheet(too big to upload). I delete some of them.
    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)

Similar Threads

  1. Daily Task Report has End Date, then the status should change automatically to Completed
    By santnair0599 in forum Excel General
    Replies: 10
    Last Post: 06-01-2015, 08:41 AM
  2. Take value from same cell daily and fill date wise
    By s_komail in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-14-2014, 03:10 PM
  3. Find a average of daily wise date
    By mukesh4821 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 09-26-2014, 06:34 AM
  4. [SOLVED] date wise /name wise total with round function
    By hkbhansali in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-03-2014, 11:37 AM
  5. macro to reformat a daily report that has different amounts of names and data on it daily
    By neoazra in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-01-2014, 10:37 PM
  6. Build and print a daily report with defined date range
    By soc.com in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-18-2013, 06:16 AM
  7. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM

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