1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

This month Last month in excel

Discussion in 'Ask an Excel Question' started by Svmaxcel, May 15, 2018.

  1. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    I have the data which has multiple dates.

    I want to categorized it based on below.

    Last 3 hours
    Today
    Yesterday
    This Week
    Last Week
    This Month
    Last Month

    If the date is 15 may
    I use =If(a2=today(),"Today",)

    I have 4 columns in my sheet as

    Date Day Week Month

    So if the date is of today it should be displayed as This Month.

    If date week no is same as of today it should be This Week
    Last edited: May 15, 2018
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,820
    Upload sample workbook with your data and examples of expected output.

    If you have PowerQuery/PowerPivot, this is typically much more efficient to do in data model/query (join) using date dimension table.
  3. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Attaching file for reference, text highlighted in blue is the output required.
    I don't have Power query/pivot on my organization

    Attached Files:

  4. Haz

    Haz Member

    Messages:
    60
    See if this is what you want.

    Attached Files:

  5. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Thanks for this.
    Would appreciate, if you can help me with the Time Slot bifurcation.
    I made 1 slot of 3 hours to cover 24 hours.


    I am getting value error in Week
  6. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Screen shot

    Attached Files:

  7. Haz

    Haz Member

    Messages:
    60
    If you're still using Excel 2003 you probably have to install the Analysis Toolpak to use the WEEKNUM function.
    Tools menu > Add-Ins > Analysis ToolPak

    Change the first Time Slot to 00:00 instead of 12:00.
    Also notice that 14:59 PM does't exist, use 2:59 PM or 14:59.

    Attached Files:

  8. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Hi All,

    i used
    Code (vb):
    =CHOOSE(IF(MONTH(R2)=MONTH(TODAY()),1,IF(MONTH(R2)=MONTH(TODAY())-1,2,3)),"This Month","Last Month","Old")
    but got Value error please help
  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,849
    1] Your below formula is alright and error didn't found.

    =CHOOSE(IF(MONTH(R2)=MONTH(TODAY()),1,IF(MONTH(R2)=MONTH(TODAY())-1,2,3)),"This Month","Last Month","Old")

    2] However, your formula Choose () can be removed and shortened in :

    =IF(MONTH(R2)=MONTH(TODAY()),"This Month",IF(MONTH(R2)=MONTH(TODAY())-1,"Last Month","Old"))

    3] Please upload your worksheet for us to check, should the error still happened.

    Regards
    Bosco
    Thomas Kuriakose likes this.
  10. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Got it working
    Thanks

    Now I have 3 diff columns like Day, Week and Month.
    In Day i get results like [Today, Yesterday,Old]
    In Week i get results like [This Week, Last Week, Old]
    In Month i get results like [This Month, Last Month, Old]

    What i want to say is if i can combine [Today, Yesterday, This Week, Last Week, This Month, Last Month] in a single cell rather then making 3 different cells.



    one more request here, please assist

    I have attached the file, there are 2 sheets Data and Dashboard

    Data is the raw which i received and I want to collate it in a better way

    So in case i select Today in [Dashboard]D5, only today score should reflect asper the department, I am not able to explain it but looking at the file you willl understand.

    WHich formula shoudl i use to get data

    Attached Files:

    Last edited: May 22, 2018
  11. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,849
    Try....

    1] Setup a "List Table" in range N1: P4 for Day, Week and Month

    2] Data Validation in C5 >> Allow: List >> Source:

    =$N$1:$P$1

    3] Data Validation in D5 >> Allow: List >> Source:

    =OFFSET($M$1,1,MATCH($C$5,$N$1:$P$1,0),3)

    4] In E10, copied across and down :

    =IF($C10="","",SUMIFS(Data!F:F,Data!$I:$I,$C10,Data!$J:$J,$D10,CHOOSE(MATCH($C$5,$N$1:$P$1,0),Data!$C:$C,Data!$D:$D,Data!$E:$E),$D$5))

    5] In H10, copied down :

    =IF(C10="","",SUM(E10:G10))

    Regards
    Bosco

    Attached Files:

    Last edited: May 22, 2018
    Thomas Kuriakose likes this.
  12. Svmaxcel

    Svmaxcel Member

    Messages:
    47
    Excellent Buddy
    this worked well

    however in this i have to select c5 & d5 both.
    can we combine it as in, D5 should have option like Today, Yesterday, This Week, Last Week....... and so on
    to make it easier for user
  13. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,849
    Maybe............

    1] Setup a "List Table" for Day/Week/Month in N1:N10

    2] Data Validation in D5 >> Allow: List >> Source:

    =$N$2:$N$10

    3] In E10, copied across and down :

    =IF($C10="","",SUMIFS(Data!F:F,Data!$I:$I,$C10,Data!$J:$J,$D10,CHOOSE(MATCH(1,1-ISERR(SEARCH({"day";"week";"month"},$D$5)),0),Data!$C:$C,Data!$D:$D,Data!$E:$E),IF(ISNUMBER(FIND("Old ",$D$5)),"Old",$D$5)))

    4] In H10, copied down :

    =IF(C10="","",SUM(E10:G10))

    Regards
    Bosco

    Attached Files:

    Svmaxcel likes this.

Share This Page