Dates in Table

[claven123]

I have a table set up like a check register. I have a date column names Date with full date ie 12/15/2023. I have a slicer set up for date, but want to limit it to only the month, not each individual date in the register. So, I set up another column with Month. The formula in that column is =[@Date]. I then format the column to only show MMM. I then set up a slicer with that column. So, the slicer shows the months ie Jan, Feb, Mar etc..

The issue is when I click on say, Jan (slicer) it only shows 1/1/2024, same for Feb and Mar etc... I thought maybe the format was off, ie showing dd/mm/yyyy, but I'm in the US and the clock/date is set to US region. So, I put in some dates like 12/1/2024, 12/18/2024 and when I click on the slicer for Dec it only shows the 12/1/2024 items.

Dennis

 

[johnny51981]

Try changing your formula to =TEXT(MONTH([@[Date]]),"Mmm")
Your current formula is essentially just pointing to the full date rather than just the month, even though your formatting shows only the month.

 

[claven123]

Try changing your formula to =TEXT(MONTH([@[Date]]),"Mmm")
Your current formula is essentially just pointing to the full date rather than just the month, even though your formatting shows only the month.

Interestingly, it change ALL the dates to Jan (left side of the cell) in the Month column. I have Jan, Feb and Dec dates.


D

 

[claven123]

When I change it to this.... =TEXT(MONTH([@Date]),"Mmm/yyyy/dd")
I get this in the cell.

First is the date columnd

1/30/2024​

2/1/2024​

12/1/2024​

12/18/2024​

12/19/2024​

Month column (with the formula above)

Jan/1900/01​

Jan/1900/02​

Jan/1900/12​

Jan/1900/12​

Jan/1900/12​

The formatting in the date cell is *3/14/2012 as Date

D

 

[johnny51981]

No, you need to leave it as "Mmm". this will give you just Jan, Feb, Mar, etc. =TEXT(MONTH([@Date]),"Mmm/yyyy/dd")

 

[johnny51981]

Oh sorry I didn't see the first reply. I think your Dates in your table aren't formatted as dates correctly, so it is giving you a bad return.

 

[claven123]

The format in the column is set as above: *3/14/2012 as Date
I've changed it to mmm/dd/yyyy and same issue. The table is connected to power query as well. I went into the that and changed the format as best I could.

I'm not sure how to fix it.

d

 

[claven123]

Book1slicertest.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2
3			Item	Date	Stuff column	Items	Month				Item	Date	Stuff column	Items	Month
4			car	1/1/24	blue	one	Jan				car	1/1/24	blue	one	Jan
5			boat	1/6/24	orange	five	Jan				boat	1/6/24	orange	five	Jan
6			Jeep	1/23/24	blue	go	Jan				jeep	1/23/24	blue	go	Jan
7			car	2/9/24	orange	sum	Feb				car	2/9/24	orange	one	Jan
8			boat	2/26/24	blue	one	Feb				boat	2/26/24	blue	five	Jan
9			Jeep	3/14/24	orange	five	Mar				jeep	3/14/24	orange	go	Jan
10			car	3/31/24	blue	go	Mar				car	3/31/24	blue	one	Jan
11			boat	4/17/24	orange	sum	Apr				boat	4/17/24	orange	five	Jan
12			Jeep	5/4/24	blue	one	May				jeep	5/4/24	blue	go	Jan
13			car	5/21/24	orange	five	May				car	5/21/24	orange	one	Jan
14			boat	6/7/24	blue	go	Jun				boat	6/7/24	blue	five	Jan
15			Jeep	6/24/24	orange	sum	Jun				jeep	6/24/24	orange	go	Jan
16			car	7/11/24	blue	one	Jul				car	7/11/24	blue	one	Jan
17			boat	7/28/24	orange	five	Jul				boat	7/28/24	orange	five	Jan
18			Jeep	8/14/24	blue	go	Aug				jeep	8/14/24	blue	go	Jan
19			car	8/31/24	orange	sum	Aug				car	8/31/24	orange	one	Jan
20			boat	9/17/24	blue	one	Sep				boat	9/17/24	blue	five	Jan
21			Jeep	10/4/24	orange	five	Oct				jeep	10/4/24	orange	go	Jan
22			car	10/21/24	blue	go	Oct				car	10/21/24	blue	one	Jan
23			boat	11/7/24	orange	sum	Nov				boat	11/7/24	orange	five	Jan
24			Jeep	11/24/24	blue	one	Nov				jeep	11/24/24	blue	go	Jan
25			car	12/11/24	orange	five	Dec				car	12/11/24	orange	one	Jan
26			boat	12/28/24	blue	go	Dec				boat	12/28/24	blue	five	Jan
27
28
29
Sheet1

Cell Formulas
Range	Formula
H4:H26	H4	=[@Date]
P4:P26	P4	=TEXT(MONTH([@Date]),"Mmm")

The slicers didn't copy over to this. Are we not supposed to use slicer to filter a date range from a table?

D

 

[Alex Blakenburg]

If you change the month formula to this, does it do what you need ?

=TEXT([@Date],"mmm")

 

[claven123]

Yes, that worked.
I should have actually LOOKED at the syntax, TEXT(value, format_text)
You know how you just overlook something and its staring you in the face the whole time.

D