Having a returned date format actually equal itself

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
I have a spreadsheet that has a running column of dates, e.g. 1/1/07, 1/2/07, etc.

I have associated columns that break this out for pivot-table purposes into things like WEEKNUM. I also have columns that just reference this column with the formatting changed to something custom like DDD or YYY to return just the year or the day of the week.

Problem is in my pivot table, when I use these DDD or YYY columns as a parameter, it actually uses the underlying valu, which si the date.

So, I can't sort by volume on Monday because excel doesn't see my cell as MON, it sees it as 1/1/07, 1/8/07, 1/15/07, etc.

Ideas/ ?
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Why not use the pivot tables grouping features?
 

brian.wethington

Well-known Member
Joined
Jul 20, 2006
Messages
1,739

ADVERTISEMENT

Perhaps I am being a little naive... can this be done by monday's, tuesdays.....

I can't only group by week 1/01 to 1/07 or something like this. am I missing it?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
Brian

I'm not 100% sure about all the grouping functionality for pivot tables.

But then again I'm not 100% sure what the OP actually wants to do.

Grouping just by a weekday doesn't really make much sense to me.:eek:

I mean I think you'd end up grouping by Monday or whatever for all months and years the data covers.:)
 

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
TEXT worked. Weekday also worked, but i had a similar column with month and using =TEXT(A1,"mmm") worked great. Thanks!
 

Parafly9

New Member
Joined
Aug 3, 2007
Messages
11
I think its a good idea; but I am trying to be able to use this pivot table for as much data as possible, including pulling graphs on if you wanted Mondays in December vs. Tuesdays in February or project-length. Anyways I got it working; thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,119
Messages
5,599,821
Members
414,341
Latest member
Mohammedsobhey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top