+ Reply to Thread
Results 1 to 10 of 10

Compare daywise monthly data

  1. #1
    Forum Contributor
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    123

    Compare daywise monthly data

    Hello friends,
    Please help me out
    I have days A7 to A37 and data for each month from Apr-23 to Jun-24 is B7 to P37.
    Days B3 to P2 compare with data from B3 to P3.
    Only two comparisons are made, April-23 and May-23, color marked manually for reference.
    Now my requirement is to get comparative data with cell color for any month or day.
    If needed please use helper
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Compare daywise monthly data

    Administrative Note:

    Is your forum profile showing the Excel PRODUCT that you need this to work for?

    Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

    The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Compare daywise monthly data

    SHOW us what you mean - mock it up.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,504

    Re: Compare daywise monthly data

    With your sample data for April and May 2023, it's not difficult to auto-fill colors.
    However, for other months, I'm unsure if you want to fill colors based on the number of days in which column at row 3?
    Please mock up a few more columns.
    Quang PT

  5. #5
    Forum Contributor
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    123

    Re: Compare daywise monthly data

    Hello friends,
    First of all very sorry. Really sorry for such a late replay.
    I was caught up in some personal issues. Anyway back to the excel sheet problem.
    I have done the manual changes as mentioned by Bebo and AliGW so can explain you accordingly

    A) Green Highlight

    1) A5 "start day" is the starting point where the comparison begins
    2) A5 to P5 will input the day for comparison
    3) B6 to P6 will calculate and display the data already shown in blue highlighted color
    What exactly do I want now (conditional formatting)
    A8 to P38 should be automatically highlighted (green manually highlighted) when inserting any day in A5 to P5 (Day 1 to 31).
    E.g. Start day (A5) is 5 and B12 to B14 Apr-23 for (B5) 7 in A12 to A14 Apr-23 my green color will appear automatically.

    B) Yellow highlight

    C1 to F3 yellow highlighted box
    Here also from C2 to F2 will insert start day and select month from D1,E1,F1 dropdown then D3 to F3 data will show and highlight yellow color (conditional formatting) in A8 to P38.

    Any month and day it should show the data

    Please help please and once again sorry for late replay.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Compare daywise monthly data

    Four conditional formatting rules:
    For column A:
    1. For green: =AND(A8>=A$5,A8<=MAX(B$5:P$5))
    2. For yellow: =AND(A8>=C$2,A8<=MAX(D$2:F$2))
    For columns B:P
    1. For green: =AND($A8>=$A$5,$A8<=B$5)
    2. For yellow: =AND($A8>=$C$2,$A8<=INDEX($D$2:$F$2,MATCH(B$4,$D$1:$F$1,0)))
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Contributor
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    123

    Re: Compare daywise monthly data

    Awesome sir thank you very much.
    Conditional formatting is working fine. Even the dropdown conditional formatting works very well,
    But once the dropdown (D1,E1,F1) changes the data for that month (D3,E3,F3) also needs to be calculated which doesn't happen.
    Sir please provide solution

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Compare daywise monthly data

    Make the following modification to the formula in cell D3 and then drag the fill handle to the right:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  9. #9
    Forum Contributor
    Join Date
    08-07-2012
    Location
    India
    MS-Off Ver
    Microsoft office home and student 2016 (Windows 10 Home 64bit)
    Posts
    123

    Re: Compare daywise monthly data

    hats off to you thank you very much

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Compare daywise monthly data

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] Daywise average for the week from Shift-wise data
    By nagesh.tvsr in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-12-2024, 09:38 AM
  2. [SOLVED] Unable to compare monthly usage data in bar chart
    By Ravindran Raman in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 09-27-2016, 08:55 AM
  3. Finding charges daywise slap rate
    By rajjana in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2015, 09:54 AM
  4. compare monthly figures in one chart
    By Davycc in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-20-2014, 02:08 PM
  5. Formula to insert shift daywise
    By akashmenon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2014, 01:43 AM
  6. Compare and Match monthly data
    By Sandiesy in forum Excel General
    Replies: 4
    Last Post: 07-28-2010, 04:57 PM
  7. [SOLVED] Formula to compare monthly due dates
    By cpope in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-25-2010, 11:16 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1