• 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...

  • 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

Svmaxcel

Member
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:
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.
 
Attaching file for reference, text highlighted in blue is the output required.
I don't have Power query/pivot on my organization
 

Attachments

  • Workbook(1).xls
    14 KB · Views: 8
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
 
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.
 

Attachments

  • Copy of Workbook(1).xls
    38 KB · Views: 2
Hi All,

i used
Code:
=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
 
Hi All,

i used
Code:
=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
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
 
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
 

Attachments

  • example.xlsx
    11.4 KB · Views: 2
Last edited:
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
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
 

Attachments

  • DayWeekMonth(1).xlsx
    13 KB · Views: 6
Last edited:
Got it working
Thanks
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

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
 
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
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
 

Attachments

  • DayWeekMonth(2).xlsx
    13.1 KB · Views: 3
Back
Top