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 at 3:45 PM.

  1. Svmaxcel

    Svmaxcel New Member

    Messages:
    29
    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 at 3:53 PM
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,556
    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 New Member

    Messages:
    29
    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:
    30
    See if this is what you want.

    Attached Files:

  5. Svmaxcel

    Svmaxcel New Member

    Messages:
    29
    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 New Member

    Messages:
    29
    Screen shot

    Attached Files:

  7. Haz

    Haz Member

    Messages:
    30
    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 New Member

    Messages:
    29
    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

Share This Page